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

is this table function possible for IBM DB2 v8.2?

P: 21
is this table function possible for IBM DB2 v8.2?


CREATE FUNCTION PEGASUS.readOrder( wsLoadNumberChar CHARACTER(07),
wsDispatchYearFlg 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_LOADNUMBERFOUNDFLG CHARACTER (01)
, ORD_LOADNUMBERNOTFOUNDFLG CHARACTER (01)
, ORD_ONWAREHOUSEFlG CHARACTER (01)
, ORD_CRITICALERRORFLG CHARACTER (01))
LANGUAGE SQL
SPECIFIC PEGASUS.readOrder
READS SQL DATA
--------------------------------------------------------------------------------------------------
-- SQL UDF (Scalar)
--------------------------------------------------------------------------------------------------
F1: BEGIN ATOMIC

DECLARE wsLoadNumberFoundFlg CHAR(01) DEFAULT ' ';
DECLARE wsLoadNumberNotFoundFlg CHAR(01) DEFAULT ' ';
DECLARE wsCriticalErrorFlg 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.TORDER"' ||
' WHERE ORD_NBR_CH = ?' ||
' AND REQ_TYP_C = ''ORDER''' ||
' WITH UR';

PREPARE v_SQL_stmt FROM CsrStmt;
OPEN loadCsr USING wsLoadNumberChar;
FETCH loadCsr
INTO wsOrdI
, wsPkpD
, wsEmtD
, wsDspStt
, wsLstDspNbr
, wsDivC
, wsPreLoadTrlr
, wsCurOrdSttC;

CASE SQLCODE
WHEN 0 THEN
SET wsLoadNumberFoundFlg = '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.TORDER_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 wsLoadNumberChar;
FETCH loadCsrW
INTO wsOrdI
, wsPkpD
, wsDspStt
, wsLstDspNbr
, wsCurOrdSttC;
CASE SQLCODE
WHEN 0 THEN
SET wsOnWarehouseFlag = 'Y';
SET wsLoadNumberFoundFlg = 'Y';
WHEN +100 THEN
IF wsDispatchYearFlg = 'N' THEN
SET wsLoadNumberNotFoundFlg = 'Y';
END IF;
ELSE
SET wsCriticalErrorFlg = 'Y';
CLOSE loadCsrW;
CLOSE loadCsr;
END CASE;
CLOSE loadCsrW;
--END-1211A
ELSE
SET wsCriticalErrorFlg = '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_LOADNUMBERFOUNDFLG
, ORD_LOADNUMBERNOTFOUNDFLG
, ORD_ONWAREHOUSEFlG
, ORD_CRITICALERRORFLG)
VALUES ( wsOrdI
, wsPkpD
, wsEmtD
, wsDspStt
, wsLstDspNbr
, wsDivC
, wsPreLoadTrlr
, wsCurOrdSttC
, wsLoadNumberFoundFlg
, wsLoadNumberNotFoundFlg
, wsOnWarehouseFlag
, wsCriticalErrorFlg);

RETURN TABLE
END
Jun 22 '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.