Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2007
    Posts
    12

    Unanswered: Create Temp Table from Cursor?

    I have this stored proc that does a ton of calculations, builds a result set of over 50 columns, and then returns it to the screen. It works perfectly.

    Now, I'm trying to bring the result set back to a second stored proc. I achieve that like this:

    DECLARE rSet RESULT_SET_LOCATOR VARYING;
    /* Call the stored procedure */

    CALL DB.GETRESULT_VER10(15026,0,0,0);
    ASSOCIATE RESULT SET LOCATORS (rSet) WITH PROCEDURE DB.GETRESULT_VER10;
    ALLOCATE C1 CURSOR FOR RESULT SET rSet;
    OPEN C1;

    Now, I need to sum the data in column 49 for a comparison program.

    I'm wondering if there is a quick way to convert a result set to a temp table or if I can manipulate the data in the cursor itself?

    I know I can get the data back to a temp table by creating a temp table by defining all 50 columns, then fetching the cursor in a loop into 50 different variables, and inserting those 50 variables into the 50 columns.

    But instead of writing all that code just to transfer my cursor data back into a form that I can work with seems to be a little much. I'm only interested in 1 column. I'm just wondering if there is a quick way to get my cursor back into a temp table that I can query.

    If anyone knows of a way to do this, please share. Google isn't being my friend right now.

    Thanks in advance.

    --Ethan

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Unfortunately, there is no way to convert a result set to a temp table. You have to fetch each row and do the processing on it, e.g. insert it in your on DGTT or CGTT.

    Note, however, that even if there was such an implicit conversion, DB2 would still have to do the same under the covers: fetch each row and insert it into the temp table.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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