467,209 Members | 1,324 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

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

how to declare temp tables in a user-defined function

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
  • viewed: 1658
Share:

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
5 posts views Thread by Billy Cormic | last post: by
6 posts views Thread by pb648174 | last post: by
2 posts views Thread by matt@fruitsalad.org | last post: by
5 posts views Thread by Jay | last post: by
21 posts views Thread by Boris Popov | last post: by
17 posts views Thread by Jon Ole Hedne | last post: by
9 posts views Thread by BillCo | last post: by
1 post views Thread by serge | last post: by
4 posts views Thread by robert d via AccessMonster.com | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.