Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2007
    Posts
    6

    Unanswered: Get multiple resultsets from tables

    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)

    http://img502.imageshack.us/img502/6...tscreencv5.png

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

    http://img526.imageshack.us/img526/960/endscreenqt4.png

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


    Code:
    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.RECORDLINES.REFERENCENUMBER = EACH_ROW.REFERENCENUMBER
        	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:

    Code:
    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
    Last edited by Camfa; 12-07-07 at 04:37.

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    This is not DB2 syntax you are using, right? The error message is also not a DB2 error message. If it is DB2, you should tell us which version of DB2 you are running on which platform.

    Also, why do you make it so complicated: figure out the rows in two steps, insert them in a temp table and then select it from the temp table? A simple SELECT statement with a subquery (or join) would be sufficient:
    Code:
    SELECT t1.id,
           t1.referencenumber,
           t1.amount
    FROM   uni452bfub.recordlines AS t1
    WHERE  t1.column-name IN
              ( SELECT t2.column-name
                FROM   uni452bfub.mainrecords )
    Note that I have no clue what the identifiers UNI452BFUB. SROBTR.INREFX and BTREFX are referring to. You don't have columns with such names in your tables, and you didn't tell us what kind of table UNI452BFUB.SROBTR is supposed to be. In any case, using the table SROBTR in that context does not make any sense to me because you did not include that table in the FROM clause. So you have to figure out what to put in the two places where I wrote column-name.

    A couple more notes:
    • Running a SELECT COUNT(*) on the query to determine the number of rows is very often not a good thing. You are wasting twice the time and resources, impact concurrence for no good reason. Usually, one just selects all the data and then fetches until SQLCODE +100 (SQLSTATE '02000') is encountered.
    • Please don't get me wrong: I think you have not yet worked a lot with SQL. I recommend that you attend a course (or read a good book) on the relational data model to understand the underlying mathematical concepts here. That will help you tremendiously when writing queries.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Dec 2007
    Posts
    6
    Actually, i AM using DB2 SQL syntax. This script is supposed to be in a CREATE STORED PROCEDURE statement (but i left those away, cause they arent necessairy for my question).

    The solution you give me, only gives me 1 resultset... As you should've read, i want multiple resultsets, one resultset for each MAINRECORD line.


    For your information, i've editted the unknown identifiers, that was a typo.


    Instead of the select count(*), i've also used a max() cause the ID's of the MAINRECORDS are unique identifiers and auto increment. That would be an option too.....

    ps: fyi: i've worked a lot with SQL, but not in DB2. I've written what i wanted first in SQL SERVER 2005, and i managed to get the correct result in 2 minutes. In DB2, i'm already looking for it for about 2 days and a half now, and still no result :s

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    We didn't knew before that this is a stored procedure (it could have been a user-defined function (UDF) or a compound statement).

    For stored procedures, you have to declare a cursor for each result set that should be returned (using the WITH RETURN clause), open that cursor and keep it open after the end of the procedure. (The RETURN statement does something different and is not applicable here.)

    Since you don't know how many cursors you need, you have to stick to dynamic SQL.

    Also, could you explain why exactly you need multiple result sets in your application? Since all the results have the same structure, you could combine them into a single result set, apply some ordering/grouping and then process everything in a single sweep in your application. Usually, that's much easier and better performing, too.

    p.s: In general, you cannot rely at all on "SELECT MAX(id) ..." giving you the number of rows in a table. There can be gaps in the IDs - unless you go to extreme measures to re-arrange IDs and close those gaps. Anyway, counting records is not needed in your scenario, it seems.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Dec 2007
    Posts
    6
    Allright, thanks, your answer brings me a little bit closer to the solution... now, so i understand that, for every result set you want to return, you need a cursor. But i have to create the cursor dynamically (through a loop or something?), but how exactly does this happen?

    It is really really necessairy to return multiple resultsets. I am using a custom written StoredProcedure adapter in my Microsoft Biztalk application that can call DB2 Storec Procedures. I haven't written the adapter, so i cant edit the source code. I realise already a long time that it would've been much more handy if i could just make a table with al the records, sort them, and send one full resultset to Biztalk, but unfortunately, that ain't gonna happen here

    Thanks already for the response!
    Andrew

  6. #6
    Join Date
    Dec 2007
    Posts
    6
    Doublepost, srry
    Last edited by Camfa; 12-07-07 at 06:35.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •