By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,017 Members | 951 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,017 IT Pros & Developers. It's quick & easy.

how to declare temp tables in a user-defined function

P: 21
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
Jun 26 '07 #1
Share this question for a faster answer!
Share on Google+

Post your reply

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