Get multiple resultsets from multiple tables

[Camfa]

Hi all,

I’ve been messing around with the following question for 2 days now, and i still haven’t find a fix. So i hope you can help me.

I’ll quickly show you guys the situation:

There are 2 tables in my database (MainRecords and RecordLines)

What i now want to return is multiple resultsets, 1 resultset for each line in the MainRecords table, like this:

The receiving of multiple resultsets is necessairy! (one resultset is no option).

I’ve written the following script. The script uses 2 for loops, the First to get over every “Main” record, and the second to process al the RecordLines that are referred to the main record.:

    SET @MAXROWS = 'select max() from UNI452BFUB.MAINRECORDS;';
 
 
 
    SET @ROWCNT = 1;
 
 
 
    FOR_LOOP: FOR EACH_ROW AS C1 CURSOR FOR
 
        select distinct REFERENCENO, COMPANYCODE from UNI452BFUB.MAINRECORDS DO
 
 
 
        FOR_LOOP2: FOR EACH_ROW2 AS C2 CURSOR FOR
 
            SELECT
 
                UNI452BFUB.RECORDLINES.ID,
                UNI452BFUB.RECORDLINES.REFERENCENUMBER,
                UNI452BFUB.RECORDLINES.AMOUNT            
 
            FROM
 
                UNI452BFUB.RECORDLINES
 
            WHERE
 
                UNI452BFUB . SROBTR . INREFX = EACH_ROW.BTREFX
 
            DO
 
            INSERT INTO UNI452BFUB.TEMP(ID, REFERENCENUMBER, AMOUNT) VALUES(EACH_ROW2.ID, EACH_ROW2.REFERENCENUMBER, EACH_ROW2.AMOUNT);    
 
        END FOR;
        RETURN(
            SELECT
                           UNI452BFUB.TEMP.ID,
                           UNI452BFUB.TEMP.REFERENCENUMBER,
                           UNI452BFUB.TEMP.AMOUNT            
                      FROM
                           UNI452BFUB.TEMP)
 
    END FOR;

But unfortunately, this totally won’t work. I never get any results, only errors, errors, errors… like the following, i get when i try to create the stored procedure which contains this query:

SQL State: 42601
Vendor Code: -199
Message: [SQL0199] Keyword END not expected. Valid tokens: UNION EXCEPT INTERSECT. Cause . . . . . :   The keyword END was not expected here.  A syntax error was detected at keyword END.  The partial list of valid tokens is UNION EXCEPT INTERSECT. This list assumes that the statement is correct up to the unexpected keyword.  The error may be earlier in the statement but the syntax of the statement seems to be valid up to this point. Recovery  . . . :   Examine the SQL statement in the area of the specified keyword.  A colon or SQL delimiter may be missing. SQL requires reserved words to be delimited when they are used as a name. Correct the SQL statement and try the request again.

Processing ended because the highlighted statement did not complete successfully

The “END” that is marked is the last end that you can find in the query above.

So, does anyone have an idea why it isn’t working, or does somebody have a much much better solution to this problem?

Thanks in advance!


Andrew