Multiple Temp Tables

[kizmar]

You'll have to excuse me as I'm familiar with T-SQL (SQL Server), not so much DB2 when creating stored procedures...

I'm trying to create a procedure where I build multiple temp tables (DECLARE GLOBAL TEMPORARY TABLE), insert data into each, then use those tables to insert data into a main temp table to be returned to a Crystal report.

I can create the procedure when I declare the table and insert some records:
CREATE PROCEDURE <DB>.<Name> (

IN <ParamName> INTEGER
)
LANGUAGE SQL
RESULT SETS 1
BEGIN
-- This table hold the final set of data for the report
-- One record per store per SKU
DECLARE GLOBAL TEMPORARY TABLE Stores (
<Column List>
) ;
END

...and I can create the procedure if I throw a simple cursor to return a result set:
CREATE PROCEDURE <DB>.<Name> (

IN <ParamName> INTEGER
)
LANGUAGE SQL
RESULT SETS 1
BEGIN

DECLARE C_RETURN CURSOR WITH RETURN FOR

SELECT *
FROM <DATABASE.TABLE> ;

OPEN C_RETURN ;

END

If I create the temp table, then put the cursor statement below it to select and return the contents of that table, it's throwing errors like "Token 'C_RETURN' not valid. Valid tokens: GLOBAL." on the first line of:
DECLARE C_RETURN CURSOR WITH RETURN FOR
SELECT *
FROM SESSION.Stores ;
OPEN C_RETURN ;


Any help would be greatly appreciated.

[Hevan]

Hi,

Try like this

P1: BEGIN

DECLARE GLOBAL TEMPORARY TABLE SESSION.test (num integer)
WITH REPLACE ON COMMIT PRESERVE ROWS ;

p2: begin
declare cur1 cursor with return for
select * from session.test;

insert into session.test values (1);
commit ;

open cur1;

END P2 ;
end p1

[santoshsri]

Hi ,

I want the data of temporary table to be displayed just after insertion the data. What should be the synatx for that ?