hi all
i need to declare a temporary table within a user-defined function in IBM DB2 ver 8. can someone give me the syntax for the temp table declaration.
CREATE FUNCTION PEGASUS.readOrder( wsLoadNumberChar CHARACTER(07),
wsDispatchYearFlg CHARACTER(01) )
RETURNS TABLE (ORD_ORDI INTEGER
, ORD_PKPD DATE
, ORD_EMTD DATE
, ORD_DSPSTT CHARACTER (01)
, ORD_LSTDSPNBR INTEGER
, ORD_DIVC CHARACTER (10)
, ORD_PRELOADTRLR INTEGER
, ORD_CURORDSTTC CHARACTER (10))
SPECIFIC PEGASUS.readOrder
LANGUAGE SQL
READS SQL DATA
------------------------------------------------------------------------
-- SQL UDF (Table)
------------------------------------------------------------------------
BEGIN ATOMIC
DECLARE TEMPORARY GLOBAL TABLE session.ReadOrder
(wsSQLCODE INTEGER
--other variables);
ON COMMIT DELETE ROWS
--do the select query
--insert it into the temp table along with the sqlcode
--return select the values in the temp table
RETURN
SELECT ORD_I
, DATE(PKP_BEG_S)
, DATE(DEL_BEG_S)
, DSP_STT
, LST_DSP_NBR
, DIV_C
, PRL_TRL_EQP_I
, CUR_ORD_STT_C
FROM PEGASUS."ALI.TORDER"
WHERE ORD_NBR_CH = wsLoadNumberChar
AND REQ_TYP_C = 'ORDER';
END
i seem to be getting an "unexpected token" error for this. need help urgently please
thanks all
| 
