Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2011
    Posts
    34

    Unanswered: WHILE LOOP with SQL statement

    Hi Guys,

    Is it possible to do a WHILE loop in a SELECT statement?

    I am trying to pull a list of customers first successful purchase for a discount offer. can someone help...

    DATA
    CUSTID ORDERID STATUS
    11111 00001 CANCELLED
    11111 00002 SUCCESSFUL
    22222 00003 SUCCESSFUL
    33333 00004 CANCELLED
    33333 00005 CANCELLED
    33333 00006 SUCCESSFUL

    I am doing a rank on CUSTID and ORDERID to get the transactions in order but some people have 2 or 3 cancelled orders before a successful one.

    could I do a WHILE loop in the WHERE clause?

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by nadecian View Post

    could I do a WHILE loop in the WHERE clause?
    No, but you don't need it.

    select custid, min(orderid) from yourtable where status = 'successful' group by custid
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    No, you cannot do a loop in the where clause. Why not just something like:

    select custid,min(orderid)
    from mytable
    where status = 'SUCCESSFUL'
    group by custid



    Andy

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    What you could do is to write a UDF, which has the loop in its body. Then use the UDF as expression in the WHERE clause. However, I would highly recommend that you investigate whether you really need the loop. Very often it is not needed and just would make things unnecessarily complex.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •