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?

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

Worked a treat. Many thanks!