I have to write some complex logic in stored procedures and I see that the 'common table' syntax in db2 sql would be helpful.

But I don't get how the DECLARE CURSOR RETURN FOR CLIENT and
OPEN CURSOR syntax requirements are going to work out for this.

How do I open temporary/common tables in a stored procedure and then get them into a cursor and return the records?

For me the goal would be to flexibly create a recordset/table on the fly from a range of sources and techniques and then return the version of it that I want to define as the 'final result'.

So again, how does the OPEN CURSOR syntax work?

If I just start by trying to create a table like:

WITH TEMP1 AS
(SELECT SOME STUFF);

I immediately wonder what I'm doing.

Thanks,

Kimball

In order to create, fill, the select and return data from a temporary table, you need to perform a little trick to get around the syntax.

Create procedure myproc(...)
...
DECLARE GLOBAL TEMPORARY TABLE fred ...

INSERT INTO SESSION.FRED ...

...

BEGIN
DECLARE CURSOR1 CURSOR WITH RETURN TO CLIENT FOR SELECT * from SESSION.FRED;

OPEN CURSOR1;

END;

END

HTH

Andy

So I can see how that would work, and I recognize that I can look up a lot of the details in the docs.

But I read somewhere you can create joins and such with the global tables.

So how about this?

DECLARE GLOBAL TABLE FRED;
INSERT INTO SESSION.FRED SELECT * FROM tblFriends;

DECLARE GLOBAL TABLE FRED2 ;
INSERT INTO SESSION.FRED2 SELECT * FROM tblFoes;

DECLARE GLOBAL TABLE WARFRED;
INSERT INTO SESSION.WARFRED SELECT FRIENDS.*, FOES.* FROM SESSION.FRED FRIENDS INNER JOIN SESSION.FRED2 FOES;

BEGIN
DECLARE CURSOR WARANDPEACE FOR RETURN TO CLIENT
OPEN CUSOR WARANDPEACE
END

Looks OK to me.

Andy

Thanks 10 chars!