By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,610 Members | 2,332 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,610 IT Pros & Developers. It's quick & easy.

Get multiple resultsets from multiple tables

P: 1
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.:

Expand|Select|Wrap|Line Numbers
  1.     SET @MAXROWS = 'select max() from UNI452BFUB.MAINRECORDS;';
  2.  
  3.  
  4.  
  5.     SET @ROWCNT = 1;
  6.  
  7.  
  8.  
  9.     FOR_LOOP: FOR EACH_ROW AS C1 CURSOR FOR
  10.  
  11.         select distinct REFERENCENO, COMPANYCODE from UNI452BFUB.MAINRECORDS DO
  12.  
  13.  
  14.  
  15.         FOR_LOOP2: FOR EACH_ROW2 AS C2 CURSOR FOR
  16.  
  17.             SELECT
  18.  
  19.                 UNI452BFUB.RECORDLINES.ID,
  20.                 UNI452BFUB.RECORDLINES.REFERENCENUMBER,
  21.                 UNI452BFUB.RECORDLINES.AMOUNT            
  22.  
  23.             FROM
  24.  
  25.                 UNI452BFUB.RECORDLINES
  26.  
  27.             WHERE
  28.  
  29.                 UNI452BFUB . SROBTR . INREFX = EACH_ROW.BTREFX
  30.  
  31.             DO
  32.  
  33.             INSERT INTO UNI452BFUB.TEMP(ID, REFERENCENUMBER, AMOUNT) VALUES(EACH_ROW2.ID, EACH_ROW2.REFERENCENUMBER, EACH_ROW2.AMOUNT);    
  34.  
  35.         END FOR;
  36.         RETURN(
  37.             SELECT
  38.                            UNI452BFUB.TEMP.ID,
  39.                            UNI452BFUB.TEMP.REFERENCENUMBER,
  40.                            UNI452BFUB.TEMP.AMOUNT            
  41.                       FROM
  42.                            UNI452BFUB.TEMP)
  43.  
  44.     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:

Expand|Select|Wrap|Line Numbers
  1. SQL State: 42601
  2. Vendor Code: -199
  3. 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
Dec 6 '07 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.