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.:
Expand|Select|Wrap|Line Numbers
- 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;
Expand|Select|Wrap|Line Numbers
- 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.
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