Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2003
    Location
    London
    Posts
    5

    Unanswered: Can't declare a cursor on a temp table

    I am trying to declare a cursor on a temporary table as follows:

    DECLARE GLOBAL TEMPORARY TABLE SESSION.TMP_WORKLIST AS
    (SELECT Timesheet.* FROM DB2ADMIN.Timesheet ) DEFINITION ONLY WITH REPLACE ON COMMIT PRESERVE ROWS NOT LOGGED;
    DECLARE worklist_Cursor CURSOR WITH HOLD FOR
    (SELECT WeekEnding, UserID, ClientID, ProjectID, Task, Subtask, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday, Comments
    FROM SESSION.TMP_WORKLIST );

    There are no other statements other than DECLAREs before these lines. When I try to compile, I get the error:

    SQL0104N An unexpected token "<cursor declaration>" was found following "".
    Expected tokens may include: "<SQL statement>". LINE NUMBER=25.

    If I move the DECLARE CURSOR statement to before the DECLARE TEMP TABLE it works (but I have to change it to reference a permanent table)
    Is it not possible to declare a cursor on a temp table?

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Yes it is, and there is a little trick to do it. It has to do with the syntax of a compound statement.

    In a compound statement the declare section has to be up front. So then how do you declare something that references something else before it is known? Simple, coumpound statements can contain other compound statements, so create an internal coumpound statement.


    Try it this way:

    DECLARE GLOBAL TEMPORARY TABLE SESSION.TMP_WORKLIST AS
    (SELECT Timesheet.* FROM DB2ADMIN.Timesheet ) DEFINITION ONLY WITH REPLACE ON COMMIT PRESERVE ROWS NOT LOGGED;

    begin atomic

    DECLARE worklist_Cursor CURSOR WITH HOLD FOR
    (SELECT WeekEnding, UserID, ClientID, ProjectID, Task, Subtask, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday, Comments
    FROM SESSION.TMP_WORKLIST );

    ....

    end;

    HTH

    Andy

  3. #3
    Join Date
    Sep 2003
    Location
    London
    Posts
    5

    Thumbs up

    Worked a treat. Many thanks!

Posting Permissions

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