Re: Appending rows to a cursor

From: Mathias Nilsson <mathias.nilsson_at_NOSPAMeng.ericsson.se>
Date: Mon, 31 Aug 1998 17:07:43 +0200
Message-ID: <35EABC3F.B10CB3DE@NOSPAMeng.ericsson.se>

Since my original post was somewhat confusing, I'll give it another try:

I have a function that is supposed to return a cursor based on two different cursors (or SELECT statements if that would be a better idea). The function has 7 in parameters.

CURSOR pre_cursor IS
SELECT max(a.mydate) dMax, b.organization sOrg, c.id nId FROM <tables>

WHERE <column 1>=<parameter 1>
AND   <column 2>=<parameter 2>
AND   <column 3>=<parameter 3>
AND   <column 4>=<parameter 4>
AND   <column 5>=<parameter 4>
AND   <column 6>=<column 7>

GROUP BY b.organization, c.id

This cursor gives me multiple rows consisting of 3 columns. I later use this cursor to send parameters to the second cursor (main_cursor).

The second cursor looks like this:
CURSOR main_cursor(dMax IN DATE, sOrg IN VARCHAR2, nId IN INTEGER) IS SELECT <10 different columns>
FROM <tables>

WHERE <column 1>=dMax
AND   <column 2>=sOrg
AND   <column 3>=nId
AND   <column 4>=<parameter 3>
AND   <column 5>=<column 6>

ORDER BY ... As you can see, main_cursor depends on pre_cursor: FOR temp_record IN pre_cursor
LOOP
 OPEN main_cursor(temp_record.dMax,temp_record.sOrg,temp_record.nId) /* I actually want to append rows to main_cursor */ END LOOP
RETURN main_cursor

This is what I want the function to do, but all I have gotten the function to do is return rows that depend on the last row in pre_cursor. It looks like when I am opening main_cursor in the loop, the old rows get overwritten.

If anyone has a solution for this I would certainly appreciate it. Thanks again
Mathias Received on Mon Aug 31 1998 - 10:07:43 CDT