470,831 Members | 1,997 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,831 developers. It's quick & easy.

Multiple Temp Tables

4
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.
May 24 '07 #1
2 3028
Hevan
17
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
May 25 '07 #2
Hi ,

I want the data of temporary table to be displayed just after insertion the data. What should be the synatx for that ?
Jun 21 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by Engwar | last post: by
2 posts views Thread by matt | last post: by
5 posts views Thread by Jay | last post: by
1 post views Thread by serge | last post: by
reply views Thread by sethwai | last post: by
6 posts views Thread by betbubble | last post: by
reply views Thread by mihailmihai484 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.