Just discovered something which I hope there is a work around for:

It appears that one cannot DECLARE a temporsry table and trhen DECLARE a cursor to select from that temporary table in a stored procedure. Here's my SQL:

DECLARE GLOBAL TEMPORARY TABLE RetrievedItem
(ARCHIVERETRIEVALID DECIMAL(13, 0),
AIMDOCUMENTID VARCHAR(256),
CAPTUREDATE DATE,
ROUTINGTRANSITNUMBER VARCHAR(9),
MICRACCOUNTNUMBER VARCHAR(24),
POSTINGACCOUNTNUMBER VARCHAR(24),
LOCATION VARCHAR(24),
SERIALNUMBER VARCHAR(20),
AMOUNT DECIMAL(13, 2),
ITEMTYPE VARCHAR(20),
STATUS VARCHAR(24),
SEQUENCENUMBER VARCHAR(24),
SORRYDOCINDICATOR VARCHAR(20),
APPLICATIONID VARCHAR(20),
CREDITDEBITFLAG VARCHAR(5),
IMAGELENGTH INTEGER,
AVAILABLEFLAG CHAR(1)
);

DECLARE SELECT_CURSOR CURSOR WITH RETURN FOR
SELECT * FROM SESSION.RetrievedItem;

When I try to build the proc, I get :
"An unexpected token <cursor declaration> was found follow"".

Is there some other way to declare a temp table and then select from it without using a cursor?

Thanks in advance...

That's right. I believe the declaration section of an SP, where "DECLARE CURSOR" belongs, should come before the SQL section, where DECLARE TEMPORARY TABLE should be...

Wrap the Declare GTT and the corresponding DEclare Cursor in a Compoind Statemet

Cheers
Sathyaram


CREATE PROCEDURE ........
...
...
C1 : BEGIN
DECLARE I INTEGER
....
....
BEGIN
DECLARE GLOBAL TEMP TABLE ....

DECLARE GTT_CURSOR ......

END
END C1

HTH

Sathyaram