is this table function possible for IBM DB2 v8.2?
CREATE FUNCTION PEGASUS.readOrd er( wsLoadNumberCha r CHARACTER(07),
wsDispatchYearF lg CHARACTER(01) )
RETURNS TABLE (ORD_ORDI INTEGER
, ORD_PKPD CHARACTER (10)
, ORD_EMTD CHARACTER (10)
, ORD_DSPSTT CHARACTER (01)
, ORD_LSTDSPNBR INTEGER
, ORD_DIVC CHARACTER (10)
, ORD_PRELOADTRLR INTEGER
, ORD_CURORDSTTC CHARACTER (10)
, ORD_LOADNUMBERF OUNDFLG CHARACTER (01)
, ORD_LOADNUMBERN OTFOUNDFLG CHARACTER (01)
, ORD_ONWAREHOUSE FlG CHARACTER (01)
, ORD_CRITICALERR ORFLG CHARACTER (01))
LANGUAGE SQL
SPECIFIC PEGASUS.readOrd er
READS SQL DATA
--------------------------------------------------------------------------------------------------
-- SQL UDF (Scalar)
--------------------------------------------------------------------------------------------------
F1: BEGIN ATOMIC
DECLARE wsLoadNumberFou ndFlg CHAR(01) DEFAULT ' ';
DECLARE wsLoadNumberNot FoundFlg CHAR(01) DEFAULT ' ';
DECLARE wsCriticalError Flg CHAR(01) DEFAULT ' ';
DECLARE wsPkpD CHAR(10) DEFAULT ' ';
DECLARE wsEmtD CHAR(10) DEFAULT ' ';
DECLARE wsDspStt CHAR(01) DEFAULT ' ';
DECLARE wsDivC CHAR(10) DEFAULT ' ';
DECLARE wsCurOrdSttC CHAR(10) DEFAULT ' ';
DECLARE wsOrdI INTEGER DEFAULT 0;
DECLARE wsLstDspNbr INTEGER DEFAULT 0;
DECLARE wsPreLoadTrlr INTEGER DEFAULT 0;
DECLARE CsrStmt VARCHAR (256);
DECLARE loadCsr CURSOR FOR v_SQL_stmt;
DECLARE loadCsrW CURSOR FOR v_SQL_stmt;
SET CsrStmt = '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.TO RDER"' ||
' WHERE ORD_NBR_CH = ?' ||
' AND REQ_TYP_C = ''ORDER''' ||
' WITH UR';
PREPARE v_SQL_stmt FROM CsrStmt;
OPEN loadCsr USING wsLoadNumberCha r;
FETCH loadCsr
INTO wsOrdI
, wsPkpD
, wsEmtD
, wsDspStt
, wsLstDspNbr
, wsDivC
, wsPreLoadTrlr
, wsCurOrdSttC;
CASE SQLCODE
WHEN 0 THEN
SET wsLoadNumberFou ndFlg = 'Y';
WHEN +100 THEN
--1211A-READ-ORDER-WHSE
SET CsrStmt = 'SELECT ORD_I' ||
' , DATE(PKP_BEG_S) ' ||
' , DSP_STT' ||
' , LST_DSP_NBR' ||
' , CUR_ORD_STT_C' ||
' FROM PEGASUS."ALI.TO RDER_W"'||
' WHERE ORD_NBR_CH = ?' ||
' AND REQ_TYP_C = ''ORDER''' ||
' AND DIV_C IN (''HJBT JBVAN'', ''HJBT JBDCS'', ''HJBT JBHA'')' ||
' WITH UR';
PREPARE v_SQL_stmt FROM CsrStmt;
OPEN loadCsrW USING wsLoadNumberCha r;
FETCH loadCsrW
INTO wsOrdI
, wsPkpD
, wsDspStt
, wsLstDspNbr
, wsCurOrdSttC;
CASE SQLCODE
WHEN 0 THEN
SET wsOnWarehouseFl ag = 'Y';
SET wsLoadNumberFou ndFlg = 'Y';
WHEN +100 THEN
IF wsDispatchYearF lg = 'N' THEN
SET wsLoadNumberNot FoundFlg = 'Y';
END IF;
ELSE
SET wsCriticalError Flg = 'Y';
CLOSE loadCsrW;
CLOSE loadCsr;
END CASE;
CLOSE loadCsrW;
--END-1211A
ELSE
SET wsCriticalError Flg = 'Y';
CLOSE loadCsr;
END CASE;
CLOSE loadCsr;
INSERT INTO TABLE
( ORD_ORDI
, ORD_PKPD
, ORD_EMTD
, ORD_DSPSTT
, ORD_LSTDSPNBR
, ORD_DIVC
, ORD_PRELOADTRLR
, ORD_CURORDSTTC
, ORD_LOADNUMBERF OUNDFLG
, ORD_LOADNUMBERN OTFOUNDFLG
, ORD_ONWAREHOUSE FlG
, ORD_CRITICALERR ORFLG)
VALUES ( wsOrdI
, wsPkpD
, wsEmtD
, wsDspStt
, wsLstDspNbr
, wsDivC
, wsPreLoadTrlr
, wsCurOrdSttC
, wsLoadNumberFou ndFlg
, wsLoadNumberNot FoundFlg
, wsOnWarehouseFl ag
, wsCriticalError Flg);
RETURN TABLE
END