Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2010
    Posts
    3

    Question Unanswered: Cursor help: How to select from a cursor ?

    Hi,

    I have some code like this:

    Code:
    CURSOR cur IS
    SELECT field1,
               field2,
               field3,
               CASE WHEN EXISTS(SELECT 1 FROM Table2 
                                                        WHERE field1 = ct.field1
                                                        AND   field2 = ct.field2
                                          )
               THEN 'Y' ELSE 'N'
               END AS Flag            
    FROM Cust_transactions ct
    Where ......
    
    FOR rec IN cur
                    
                LOOP
    
               Select  *
               From Table3
               Where field1 = rec.field1
              AND EXISTS ( Select from Cursor cur ...?
         
               END LOOP;

    As you can see in the above code I need to make a select in the same cursor. ...Could a cursor be used as a table ?

    The requirement is that while looping through cursor Cust_transactions , If flag = Y, I need to find out if there is at least another line with same customer id that have their flag = N.

    My soln is similar to this: (continue from above code)

    Code:
    AND rec.Flag = 'Y'
              AND EXISTS (
              Select 1 from cur
                          where customer_id = rec.customer_id
                          And Flag = 'N')
    Is this possible ? or is there another way to solve this issue


    Thanks

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Could a cursor be used as a table ?
    no

    just have the same table in the FROM clause twice
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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