Hi,

I have a following issue in DB2 UDB v8.2. How can I build some data and
store it in a temp table and return as a TABLE to a function.. This
function will be called in a SELECT stmt. something like
SELECT .... FROM TABLE(GetRecursiveCDOs()) AS RecursiveCDOs()

Here is the code: The logic is as follows.When I tried to compile the
code, it errors out at SQLCODE. Is SQLCODE permissable in this context
??

CREATE FUNCTION GetRecursiveCDOs()
RETURNS TABLE(CDODefId INTEGER)
BEGIN ATOMIC
   --
   DECLARE SQLCODE     INTEGER;
   DECLARE v_SQLStmt   VARCHAR(512);
   DECLARE n_CDODefId  INTEGER;
   DECLARE CDODefs_Cur  CURSOR WITH HOLD FOR CDODefsSQL;
   DECLARE GLOBAL TEMPORARY TABLE gt_CDODefs(CDODefId INTEGER NOT
NULL) ON COMMIT DELETE ROWS WITH REPLACE NOT LOGGED;
   --
   SET SQLCODE = 0;
   --
   SET v_SQLStmt = 'WITH RecursiveCDOs (CDODefId) AS'||
                   '( SELECT Parent.CDODefId'||
                   '    FROM CDODefinition Parent'||
                   '   WHERE Parent.CDODefId IN ( 1020, 1050 )'||
                   '  UNION ALL'||
                   '  SELECT Child.CDODefId'||
                   '    FROM RecursiveCDOs Parent'||
                   '        ,CDODefinition Child'||
                   '   WHERE Child.ParentCDOId = Parent.CDODefId'||
                   ') SELECT CDODefId FROM RecursiveCDOs';
   --
   DELETE FROM SESSION.gt_CDODefs;
   COMMIT;
   --
   PREPARE CDODefsSQL FROM v_SQLStmt;
   OPEN CDODefs_Cur;
   FETCH FROM CDODefs_Cur INTO n_CDODefId;
   --
   WHILE (SQLCODE = 0) DO
       --
       INSERT INTO SESSION.gt_CDODefs (CDODefId)
            VALUES (n_CDODefId);
       --
   END WHILE;
   --
  CLOSE CDODefs_Cur;
   --
   RETURN SELECT CDODefId
            FROM SESSION.gt_CDODefs;
END;

I have tried using a view with just the WITH statement. But our
application will drop the table inside of the view and recreates the
table. The view becomes inoperable because the table was dropped. At
present there is no mechanism to recompile the view. If I create a
function RETURNING TABLE, the table cannot be dropped and the
application hangs. As a work around, the SELECT statement is passed in
as literal to be opened as a cursor and then ith is stored in a GTT and
then query the GTT thus RETURNING the TABLE.

Is there a better way to approach this solution ??

Any ideas will be appreciated...

Thanks,
Purush