March 30, 2015 at 5:57 am
--<ScriptOptions statementTerminator=";"/>
CREATE PROCEDURE DB2TAB.MILLING_POSN_LIMITS (IN P_POSN_DATE CHAR(10))
VERSION V1
RESULT SETS 1
VALIDATE BIND
P1: BEGIN
-- Declare variables
DECLARE V_DOES_NOT_EXISTS CONDITION FOR '42704';
DECLARE V_ERR_CODE INT;
DECLARE V_POSN_DT DATE;
DECLARE V_POSN_DT_ID INTEGER DEFAULT 0;
DECLARE V_ROW_COUNT INTEGER DEFAULT 0;
DECLARE V_SP_RETVAL INTEGER DEFAULT 0;
-- Declare cursors
DECLARE CRSR1 CURSOR WITH RETURN FOR
WITH X (REC_SEQ, REC_CODE, COMD_GROUP, COMD_CODE, PRCD_CODE, REC_DESC) AS
(SELECT
1
,'P55'
,'WHEAT'
,'16'
,'F'
,'Wheat Flat Price Position'
FROM
SYSIBM.SYSDUMMY1
UNION ALL
SELECT
2
,'P75'
,'WHEAT'
,'16'
,'B'
,'Wheat Basis'
FROM
SYSIBM.SYSDUMMY1
UNION ALL
SELECT
3
,'P55'
,'CORN'
,'02'
,'F'
,'Corn Flat Price Position'
FROM
SYSIBM.SYSDUMMY1
UNION ALL
SELECT
4
,'P75'
,'CORN'
,'02'
,'B'
,'Corn Basis'
FROM
SYSIBM.SYSDUMMY1
UNION ALL
SELECT
5
,'P55'
,'SOYBEAN'
,'01'
,'F'
,'Soybean Flat Price Position'
FROM
SYSIBM.SYSDUMMY1
UNION ALL
SELECT
6
,'P75'
,'SOYBEAN'
,'01'
,'B'
,'Soybean Basis'
FROM
SYSIBM.SYSDUMMY1
UNION ALL
SELECT
7
,'P55'
,'MILO'
,'37'
,'F'
,'Milo Flat Price Position'
FROM
SYSIBM.SYSDUMMY1
UNION ALL
SELECT
8
,'P75'
,'MILO'
,'37'
,'B'
,'Milo Basis'
FROM
SYSIBM.SYSDUMMY1
)
, Y (COMD_CODE, PRCD_CODE, LONG_LIMIT, SHORT_LIMIT) AS
(SELECT
COMD_CODE
,PRCD_CODE
,LIMI_LONG_LIMIT
,LIMI_SHORT_LIMIT
FROM
DB2TAB.POSITION_TOL_LIM
INNER JOIN
DB2TAB.COMMODITY MPCOMD ON MPCOMD.COMD_ID = LIMI_COMD_ID
INNER JOIN
DB2TAB.GRENCOMD GRCOMD ON GRCOMD.COMD_ID = COMD_COMD_ID
INNER JOIN
DB2TAB.PRICE_CODE MPPRCD ON MPPRCD.PRCD_ID = LIMI_PRCD_ID
INNER JOIN
DB2TAB.GRENPRCD_CODE GRPRCD ON GRPRCD.PRCD_ID = PRCD_PRCD_ID
)
, Z (REC_CODE, COMD_CODE, POSN_VALUE) AS
(SELECT
TCPD_REC_CODE
,'16'
,TCPD_WHEAT_VALUE
FROM
SESSION.TEMP_TBL_CONSL_POSN_DATA
UNION ALL
SELECT
TCPD_REC_CODE
,'02'
,TCPD_CORN_VALUE
FROM
SESSION.TEMP_TBL_CONSL_POSN_DATA
UNION ALL
SELECT
TCPD_REC_CODE
,'01'
,TCPD_SOYB_VALUE
FROM
SESSION.TEMP_TBL_CONSL_POSN_DATA
UNION ALL
SELECT
TCPD_REC_CODE
,'37'
,TCPD_MILO_VALUE
FROM
SESSION.TEMP_TBL_CONSL_POSN_DATA
)
SELECT
V_POSN_DT AS POSN_DATE
,X.REC_SEQ AS REC_SEQ
,X.COMD_GROUP
,X.REC_DESC AS REC_DESC
,IFNULL(Y.LONG_LIMIT,0) AS LONG_LIMIT
,IFNULL(Y.SHORT_LIMIT,0) AS SHORT_LIMIT
,IFNULL(Z.POSN_VALUE,0) AS POSN_VALUE
,CASE WHEN IFNULL(Z.POSN_VALUE,0) > IFNULL(Y.LONG_LIMIT,0) THEN 'N'
ELSE CASE WHEN IFNULL(Z.POSN_VALUE,0) < IFNULL(Y.SHORT_LIMIT,0) THEN 'N'
ELSE 'Y'
END
END AS TOLERANCE_FLAG
,CASE WHEN IFNULL(Z.POSN_VALUE,0) > IFNULL(Y.LONG_LIMIT,0)
THEN IFNULL(Z.POSN_VALUE,0) - IFNULL(Y.LONG_LIMIT,0)
ELSE CASE WHEN IFNULL(Z.POSN_VALUE,0) < IFNULL(Y.SHORT_LIMIT,0)
THEN IFNULL(Z.POSN_VALUE,0) - IFNULL(Y.SHORT_LIMIT,0)
ELSE 0
END
END AS AMT_TOLERANCE
FROM
X
LEFT OUTER JOIN
YONY.COMD_CODE = X.COMD_CODE
AND Y.PRCD_CODE = X.PRCD_CODE
LEFT OUTER JOIN
ZONZ.REC_CODE = X.REC_CODE
AND Z.COMD_CODE = X.COMD_CODE
ORDER BY
REC_SEQ
WITH UR;
-- Declare handlers
DECLARE CONTINUE HANDLER FOR V_DOES_NOT_EXISTS SET V_ERR_CODE = 1;
-- Declare temp-tables
DROP TABLE SESSION.TEMP_TBL_CONSL_POSN_DATA;
DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP_TBL_CONSL_POSN_DATA
(
TCPD_REC_SEQSMALLINT
,TCPD_REC_CODECHAR(3)
,TCPD_REC_DESCCHAR(25)
,TCPD_WHEAT_VALUEDECIMAL(18,4)
,TCPD_CORN_VALUEDECIMAL(18,4)
,TCPD_SOYB_VALUEDECIMAL(18,4)
,TCPD_MILO_VALUEDECIMAL(18,4)
) ON COMMIT DROP TABLE;
IF P_POSN_DATE IS NULL OR P_POSN_DATE = '' THEN
SELECT
PSDT_ID, PSDT_POS_DATE
INTO
V_POSN_DT_ID, V_POSN_DT
FROM
DB2TAB.SCMPPSDT_POSITION_DATE
WHERE
PSDT_CURRENT_SW = 'Y';
ELSE
SELECT
DATE(P_POSN_DATE)
INTO
V_POSN_DT
FROM
SYSIBM.SYSDUMMY1;
END IF;
SELECT
PSDT_ID, PSDT_POS_DATE
INTO
V_POSN_DT_ID, V_POSN_DT
FROM
DB2TAB.SCMPPSDT_POSITION_DATE
WHERE
PSDT_POS_DATE = V_POSN_DT;
-- CALL US-POSITION
CALL DB2TAB.SPREAD_DATA (V_POSN_DT);
GET DIAGNOSTICS V_SP_RETVAL = RETURN_STATUS;
-- CALL CANAD-POSITION
CALL DB2TAB.CANADA_DATA (V_POSN_DT);
GET DIAGNOSTICS V_SP_RETVAL = RETURN_STATUS;
-- CALL CARIB-POSITION
CALL DB2TAB.CARIBBEAN_DATA (V_POSN_DT);
GET DIAGNOSTICS V_SP_RETVAL = RETURN_STATUS;
-- CALL STARCH-POSITION
CALL DB2TAB.STARCH_DATA (V_POSN_DT);
GET DIAGNOSTICS V_SP_RETVAL = RETURN_STATUS;
-- CALL MLG-DIV-POSITION
CALL DB2TAB.MILLING_DIV_DATA (V_POSN_DT);
GET DIAGNOSTICS V_SP_RETVAL = RETURN_STATUS;
-- CALL OPT-FUT-EQU-DATA
CALL DB2TAB.OPT_FUT_EQU_DATA (V_POSN_DT);
GET DIAGNOSTICS V_SP_RETVAL = RETURN_STATUS;
INSERT INTO
SESSION.TEMP_TBL_CONSL_POSN_DATA
(
TCPD_REC_SEQ
,TCPD_REC_CODE
,TCPD_REC_DESC
,TCPD_WHEAT_VALUE
,TCPD_CORN_VALUE
,TCPD_SOYB_VALUE
,TCPD_MILO_VALUE
)
SELECT
REC_SEQUENCE
,RCTP_REC_TYPE AS REC_CODE
,RCTP_REC_TYPE_DESC AS REC_DESC
,SUM(CASE WHEN GRCOMD.COMD_CODE IN ('16','Z4','42','Q7','11','13','U1','U2','10','12','15','9R','Z2','9Q','Z3','3N','W2','U4','U5','W0','U7','U9','9V','W6','U0','W8') THEN POSN_VALUE ELSE 0 END) AS "Wheat"
,SUM(CASE WHEN GRCOMD.COMD_CODE IN ('02','73') THEN POSN_VALUE ELSE 0 END) AS "Corn"
,SUM(CASE WHEN GRCOMD.COMD_CODE IN ('01') THEN POSN_VALUE ELSE 0 END) AS "Soybean"
,SUM(CASE WHEN GRCOMD.COMD_CODE IN ('37','DZ') THEN POSN_VALUE ELSE 0 END) AS "Milo"
FROM
(SELECT
TUSS_REC_SEQ AS REC_SEQUENCE
,TUSS_REC_ID AS REC_ID
,TUSS_LOC_ID AS LOC_ID
,TUSS_COMD_ID AS COMD_ID
,TUSS_TOTAL AS POSN_VALUE
FROM
SESSION.TEMP_TBL_US_SPREAD
UNION
SELECT
TCND_REC_SEQ AS REC_SEQUENCE
,TCND_REC_ID AS REC_ID
,TCND_LOC_ID AS LOC_ID
,TCND_COMD_ID AS COMD_ID
,TCND_POSN_VALUE AS POSN_VALUE
FROM
SESSION.TEMP_TBL_CANADA_DATA
UNION
SELECT
TCAD_REC_SEQ AS REC_SEQUENCE
,TCAD_REC_ID AS REC_ID
,TCAD_LOC_ID AS LOC_ID
,TCAD_COMD_ID AS COMD_ID
,TCAD_POSN_VALUE AS POSN_VALUE
FROM
SESSION.TEMP_TBL_CARIBBEAN_DATA
UNION
SELECT
TSTD_REC_SEQ AS REC_SEQUENCE
,TSTD_REC_ID AS REC_ID
,TSTD_LOC_ID AS LOC_ID
,TSTD_COMD_ID AS COMD_ID
,TSTD_POSN_VALUE AS POSN_VALUE
FROM
SESSION.TEMP_TBL_STARCH_DATA
UNION
SELECT
TMDD_REC_SEQ AS REC_SEQUENCE
,TMDD_REC_ID AS REC_ID
,TMDD_LOC_ID AS LOC_ID
,TMDD_COMD_ID AS COMD_ID
,TMDD_POSN_VALUE AS POSN_VALUE
FROM
SESSION.TEMP_TBL_MILG_DIV_DATA
UNION
SELECT
TOFD_REC_SEQ AS REC_SEQUENCE
,TOFD_REC_ID AS REC_ID
,NULLIF(1,1) AS LOC_ID
,TOFD_COMD_ID AS COMD_ID
,TOFD_POSN_VALUE AS POSN_VALUE
FROM
SESSION.TEMP_TBL_OPT_FUT_EQU_DATA
) X
INNER JOIN
DB2TAB.COMMODITY MPCOMD ON MPCOMD.COMD_ID = X.COMD_ID
INNER JOIN
DB2TAB.GRENCOMD GRCOMD ON GRCOMD.COMD_ID = COMD_COMD_ID
INNER JOIN
DB2TAB.SCMPRCTP_RECORD_TYPEONRCTP_ID = X.REC_ID
WHERE
RCTP_REC_TYPE IN ('P55','P75')
GROUP BY
REC_SEQUENCE
,RCTP_REC_TYPE
,RCTP_REC_TYPE_DESC;
GET DIAGNOSTICS V_ROW_COUNT = ROW_COUNT;
OPEN CRSR1;
END P1;
GRANT EXECUTE ON PROCEDURE "DB2TAB"."SCMP_RPT_MILLING_CONSL_POSN_LIMITS" TO "G39750" WITH GRANT OPTION;
March 30, 2015 at 6:04 am
That's not T-SQL. This is a SQL Server site, Microsoft SQL Server. You'll have far better luck on forums dedicated to whatever database engine that code is for.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 30, 2015 at 12:43 pm
I will add that you really REALLY don't want a CURSOR in a UDF.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 31, 2015 at 11:02 am
It's some version of DB2 / UDB.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply