473,320 Members | 1,993 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Get multiple resultsets from multiple tables

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
0 1901

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

Similar topics

1
by: Top Gun | last post by:
In order to avoid multiple trips to the database, I would like to fill several tables in a DataSet with a single call to a stored procedure that will return resultsets for the appropriate tables. ...
2
by: Keith B via SQLMonster.com | last post by:
Hi! I want to return a derived table along with 4 simple tables in a stored procedure as follows: Input parameter: @FtNum (==Order Number, selects one Order and all associated data)...
4
by: Amy | last post by:
Hello, I've been struggling to learn C#.NET for a while now. I've made some progress, but I'm easily stumped. :( What's stumping me today is this: I've got a stored procedure (SQL) that...
3
by: Jon Spivey | last post by:
Hi, using vb.net/sql server 2000 migrating an app from asp to asp.net. I have a stored procedure that returns 3 recordsets. In asp to move to the next recordset I'd just do <% set rs =...
0
by: Crazy Cat | last post by:
Hi, Using Visual Basic 2005 -- I have a stored procedure that returns multiple resultsets. I fill a datareader (SQLDataReader to be exact) with the results from a command object's ExecuteReader...
5
by: praveen | last post by:
Hi, I am using nested resultsets to execute queries on two different tables. The code structure is: stmt1 = conn.createStatement(); rs1 = stmt1.executeQuery(query1); while (rs1.next()) {...
1
by: Robson Siqueira | last post by:
Folks, For designing WinApps, I do prefer to have the controls dragged and dropped into the screen, mainly for datagridview controls. For that end, I normally use the DataSet designer but...
4
by: Chris | last post by:
This might be a stupid question.... I have a stored procedures, which uses two selects. When I run the SP I get two resultsets, one very big and the other much smaller, which is the one I want in...
10
by: =?Utf-8?B?UGV0ZXI=?= | last post by:
I'm using this coding to get 2 resultsets thru datareader and then load them into 2 datatables and bind the datatables to datagridviews. But sdrGrid.NextResult() is returning false for some...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.