11-10-10, 15:39 #1Registered User
- Join Date
- Oct 2010
Unanswered: Cursor help: How to select from a cursor ?
I have some code like this:
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)
AND rec.Flag = 'Y' AND EXISTS ( Select 1 from cur where customer_id = rec.customer_id And Flag = 'N')
11-10-10, 15:50 #2Registered User
Provided Answers: 1
- Join Date
- Aug 2003
- Where the Surf Meets the Turf @Del Mar, CA
>Could a cursor be used as a table ?
just have the same table in the FROM clause twiceYou 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.