IBM Mainframe Forum

by **jfdutcher** » Fri Sep 19, 2014 11:19 pm

Does it seem possible to convert this monster declare cursor statement to a SQL query that I can test and run in QMF and/or DB2 Client ?
I don't relate well to the FROMTHIS_TBL and CREATETHIS_TBL pieces but would like to run outside of a batch COBOL pgm.

Select all

DECLARE READ-CURSOR CURSOR WITH ROWSET POSITIONING FOR
WITH FROMTHIS_TBL (NWK_CODE
,BLEND_EFF_DATE
,NWK_NAME)
AS
(SELECT SUBSTR(A.PRV_BSN_RUL_SCP_DTA_ID,4,3)
,A.PRV_BSN_RUL_PCSG_DATE
,A.PRV_BSN_RUL_DTA_TX
FROM DB2TST2.T401ABRU A
WHERE A.PRV_BSN_RUL_FMT_ID = 'DPV-ICS-BLENDED-NWK'
AND A.PRV_BSN_RUL_PRSTA_CODE = 'ACTIVE')
,CREATETHIS_TBL (BLEND_NWK_CODE
,BLEND_EFF_DATE
,NWK_NAME)
AS
(SELECT SUBSTR(A.PRV_BSN_RUL_SCP_DTA_ID,1,3)
,A.PRV_BSN_RUL_PCSG_DATE
,A.PRV_BSN_RUL_DTA_TX
FROM DB2TST2.T401ABRU A
WHERE A.PRV_BSN_RUL_FMT_ID = 'DPV-ICS-BLENDED-NWK'
AND A.PRV_BSN_RUL_PRSTA_CODE = 'ACTIVE')
SELECT C.NWK_CODE
,B.NWK_STA_RSN_CODE
,B.NWK_ENR_STA_CODE
,B.NWK_ENR_EFF_DATE
,B.NWK_ENR_CNL_DATE
,D.NWK_RLE_CODE
,B.NWK_ENR_TKN
,' ' AS PRV_SAN_EFF_DATE
,' ' AS PRV_SAN_CNL_DATE
,'9999-12-31' AS BLEND_EFF_DATE
,' ' AS NWK_NAME
FROM DB2TST2.T401AHPA A
, DB2TST2.T401AENR B
, DB2TST2.T401ANWK C
, DB2TST2.T401ARLE D
, DB2TST2.T401ABUS E
WHERE A.HP_ID = :SCOPE-FIELDS.SCOPE-HP-ID
AND E.HP_ID = :SCOPE-FIELDS.SCOPE-HP-ID
AND A.HP_ID = E.HP_ID
AND A.HP_ADDR_CNL_DATE > CURRENT DATE
AND A.ADDR_TYP_CODE = 'B'
AND B.CMPL_INCMPL_IND = 'C'
AND E.INF_IPT_CODE = 'P'
AND A.HP_ADDR_TKN = B.HP_ADDR_TKN
AND A.HP_ADDR_TKN = E.HP_ADDR_TKN
AND C.NWK_TKN = B.NWK_TKN
AND B.NWK_ENR_EFF_DATE <> B.NWK_ENR_CNL_DATE
AND B.NWK_RLE_TKN = D.NWK_RLE_TKN
AND A.ANW_TSP_GRP =
(SELECT MAX(ANW_TSP_GRP)
FROM DB2TST2.T401AHPA AA
WHERE A.HP_ID = AA.HP_ID
AND A.HP_ADDR_TKN = AA.HP_ADDR_TKN
AND A.ADDR_TYP_CODE = AA.ADDR_TYP_CODE)
AND B.ANW_TSP_GRP =
(SELECT MAX(ANW_TSP_GRP)
FROM DB2TST2.T401AENR BB
WHERE B.NWK_RLE_TKN = BB.NWK_RLE_TKN
AND B.HP_ADDR_TKN = BB.HP_ADDR_TKN
AND B.NWK_TKN = BB.NWK_TKN
AND B.NWK_ENR_TKN = BB.NWK_ENR_TKN)
AND C.ANW_TSP_GRP =
(SELECT MAX(ANW_TSP_GRP)
FROM DB2TST2.T401ANWK CC
WHERE C.NWK_TKN = CC.NWK_TKN)
AND D.ANW_TSP_GRP =
(SELECT MAX(ANW_TSP_GRP)
FROM DB2TST2.T401ARLE DD
WHERE D.NWK_TKN = DD.NWK_TKN
AND D.NWK_RLE_TKN = DD.NWK_RLE_TKN)
AND E.ANW_TSP_GRP =
(SELECT MAX(ANW_TSP_GRP)
FROM DB2TST2.T401ABUS EE
WHERE E.HP_ID = EE.HP_ID
AND E.HP_ADDR_TKN = EE.HP_ADDR_TKN)
UNION
SELECT DISTINCT G.BLEND_NWK_CODE
,'61'
,'A'
,G.BLEND_EFF_DATE
,'9999-12-31'
,' '
,0
,0
,' ' AS PRV_SAN_EFF_DATE
,' ' AS PRV_SAN_CNL_DATE
,G.BLEND_EFF_DATE
,G.NWK_NAME
FROM DB2TST2.T401AHPA A
, DB2TST2.T401AENR B
, DB2TST2.T401ANWK C
, DB2TST2.T401ARLE D
, DB2TST2.T401ABUS E
,FROMTHIS_TBL F
,CREATETHIS_TBL G
WHERE A.HP_ID = :SCOPE-FIELDS.SCOPE-HP-ID
AND E.HP_ID = :SCOPE-FIELDS.SCOPE-HP-ID
AND A.HP_ID = E.HP_ID
AND A.HP_ADDR_CNL_DATE > CURRENT DATE
AND A.ADDR_TYP_CODE = 'B'
AND B.CMPL_INCMPL_IND = 'C'
AND B.NWK_ENR_STA_CODE = 'A'
AND E.INF_IPT_CODE = 'P'
AND A.HP_ADDR_TKN = B.HP_ADDR_TKN
AND A.HP_ADDR_TKN = E.HP_ADDR_TKN
AND C.NWK_TKN = B.NWK_TKN
AND C.NWK_CODE = F.NWK_CODE
AND B.NWK_ENR_EFF_DATE <> B.NWK_ENR_CNL_DATE
AND B.NWK_RLE_TKN = D.NWK_RLE_TKN
AND B.NWK_ENR_CNL_DATE >= G.BLEND_EFF_DATE
AND B.NWK_ENR_EFF_DATE =
(SELECT MAX(H.NWK_ENR_EFF_DATE)
FROM DB2TST2.T401AENR H
WHERE H.HP_ADDR_TKN = A.HP_ADDR_TKN
AND H.NWK_ENR_CNL_DATE > G.BLEND_EFF_DATE
AND H.NWK_ENR_EFF_DATE <> H.NWK_ENR_CNL_DATE
AND C.NWK_CODE IN
(SELECT I.NWK_CODE
FROM FROMTHIS_TBL I
WHERE I.NWK_CODE = G.BLEND_NWK_CODE)
AND H.ANW_TSP_GRP =
(SELECT MAX(HH.ANW_TSP_GRP)
FROM DB2TST2.T401AENR HH
WHERE H.NWK_RLE_TKN = HH.NWK_RLE_TKN
AND H.HP_ADDR_TKN = HH.HP_ADDR_TKN
AND H.NWK_TKN = HH.NWK_TKN))
AND A.ANW_TSP_GRP =
(SELECT MAX(ANW_TSP_GRP)
FROM DB2TST2.T401AHPA AA
WHERE A.HP_ID = AA.HP_ID
AND A.HP_ADDR_TKN = AA.HP_ADDR_TKN
AND A.ADDR_TYP_CODE = AA.ADDR_TYP_CODE)
AND B.ANW_TSP_GRP =
(SELECT MAX(ANW_TSP_GRP)
FROM DB2TST2.T401AENR BB
WHERE B.HP_ADDR_TKN = BB.HP_ADDR_TKN
AND B.NWK_ENR_EFF_DATE = BB.NWK_ENR_EFF_DATE)
AND C.ANW_TSP_GRP =
(SELECT MAX(ANW_TSP_GRP)
FROM DB2TST2.T401ANWK CC
WHERE C.NWK_TKN = CC.NWK_TKN)
AND D.ANW_TSP_GRP =
(SELECT MAX(ANW_TSP_GRP)
FROM DB2TST2.T401ARLE DD
WHERE D.NWK_TKN = DD.NWK_TKN
AND D.NWK_RLE_TKN = DD.NWK_RLE_TKN)
AND E.ANW_TSP_GRP =
(SELECT MAX(ANW_TSP_GRP)
FROM DB2TST2.T401ABUS EE
WHERE E.HP_ID = EE.HP_ID
AND E.HP_ADDR_TKN = EE.HP_ADDR_TKN)
AND E.HP_ADDR_TKN = EE.HP_ADDR_TKN)
UNION
SELECT DISTINCT G.BLEND_NWK_CODE
,'61'
,'A'
,G.BLEND_EFF_DATE
,'9999-12-31'
,' '
,0
,' ' AS PRV_SAN_EFF_DATE
,' ' AS PRV_SAN_CNL_DATE
,G.BLEND_EFF_DATE
,G.NWK_NAME
FROM DB2TST2.T401AHPA A
, DB2TST2.T401AENR B
, DB2TST2.T401ANWK C
, DB2TST2.T401ARLE D
, DB2TST2.T401ABUS E
,FROMTHIS_TBL F
,CREATETHIS_TBL G
WHERE A.HP_ID = :SCOPE-FIELDS.SCOPE-HP-ID
AND E.HP_ID = :SCOPE-FIELDS.SCOPE-HP-ID
AND A.HP_ID = E.HP_ID
AND A.HP_ADDR_CNL_DATE > CURRENT DATE
AND A.ADDR_TYP_CODE = 'B'
AND B.CMPL_INCMPL_IND = 'C'
AND B.NWK_ENR_STA_CODE = 'A'
AND E.INF_IPT_CODE = 'P'
AND A.HP_ADDR_TKN = B.HP_ADDR_TKN
AND A.HP_ADDR_TKN = E.HP_ADDR_TKN
AND C.NWK_TKN = B.NWK_TKN
AND B.NWK_ENR_EFF_DATE <> B.NWK_ENR_CNL_DATE
AND B.NWK_RLE_TKN = D.NWK_RLE_TKN
AND B.NWK_ENR_CNL_DATE >= G.BLEND_EFF_DATE
AND C.NWK_CODE = F.NWK_CODE
AND C.NWK_CODE = F.NWK_CODE
AND A.ANW_TSP_GRP =
(SELECT MAX(ANW_TSP_GRP)
FROM DB2TST2.T401AHPA AA
WHERE A.HP_ID = AA.HP_ID
AND A.HP_ADDR_TKN = AA.HP_ADDR_TKN
AND A.ADDR_TYP_CODE = AA.ADDR_TYP_CODE)
AND B.ANW_TSP_GRP =
(SELECT MAX(ANW_TSP_GRP)
FROM DB2TST2.T401AENR BB
WHERE B.NWK_RLE_TKN = BB.NWK_RLE_TKN
AND B.HP_ADDR_TKN = BB.HP_ADDR_TKN
AND B.NWK_TKN = BB.NWK_TKN
AND B.NWK_ENR_TKN = BB.NWK_ENR_TKN)
AND C.ANW_TSP_GRP =
(SELECT MAX(ANW_TSP_GRP)
FROM DB2TST2.T401ANWK CC
WHERE C.NWK_TKN = CC.NWK_TKN)
AND D.ANW_TSP_GRP =
(SELECT MAX(ANW_TSP_GRP)
FROM DB2TST2.T401ARLE DD
WHERE D.NWK_TKN = DD.NWK_TKN
AND D.NWK_RLE_TKN = DD.NWK_RLE_TKN)
AND E.ANW_TSP_GRP =
(SELECT MAX(ANW_TSP_GRP)
FROM DB2TST2.T401ABUS EE
WHERE E.HP_ID = EE.HP_ID
AND E.HP_ADDR_TKN = EE.HP_ADDR_TKN)
ORDER BY 1
,4 DESC
FOR FETCH ONLY OPTIMIZE FOR 77 ROWS
WITH UR;

IBM Mainframe Forum

Convert Batch Declare Cursor to SQL Query

Convert Batch Declare Cursor to SQL Query