MVSFORUMS.com

[esaccy]

Hi Can anybody please explain how the following db2 query works(Please mention step by step execution process)


Table names

[GuyC]

1: This SQL will do nothing because it contains syntax errors.
2: DB2 has no region names.
3: you can forget the left join. It will be interpreted as a normal join because in the outer where clause dvdcsmy.prcs_dt is evaluated any possible result row where prcs_dt would be NULL is removed by this where clause.
4: too much use of coalesce. In some cases (like X.MAXDATE), the column can never be NULL.
5:furthermore table X is not used anywhere and has no correlation with any other table.In fact it could be executed seperately and give the same result.

Looking at 3:,4:,5: the person who wrote this has no idea what he was doing, but I guess the query returned the result he/she was expecting given certain test-cases.
------------------
This is the kind of query you get when solving temporal data
each tablerow has a start and end date
and when joining those tables you constantly must find the row where a certain date falls between [>= <] those dates.

[esaccy]

Hi
This is running successfully.
Moreover region names are there
GDB2DW2.DVDCCAR DVDCCAR
GDB2DW2.ARXIP ARXIP etc

[esaccy]

Hi
Moreover X is a pre-defined condition and it is correct only.This is running for years successfully

[dbzTHEdinosauer]

what guyc was trying to tell you is the word DB2 REGION is incorrect.
qualifier or schema is what you meant to say.

two, next time you post, use the bbcode tags.

[code:1:f766c03ad3]
SELECT
DVDCCAR.AR_ID
,ARXIP.IP_ID
,COALESCE(DVDCSMY.PRCS_DT,'01.01.0001') AS PRCS_DT
,COALESCE(DVDCSMY.LST_STMT_BAL,0) AS LST_STMT_BAL
,COALESCE(DVDCSMY.HDYTD_NET_AMT_PRCH,0) AS HDYTD_NET_AMT_PRCH
,COALESCE(DVDCSMY.CR_LF_FL,0) AS CR_LF_FL
,COALESCE(DVDCSMY.CR_LN,0) AS CR_LN
,CAST(COALESCE(DVDCSMY.HD_PAY_HST,' ') AS CHAR(12)) AS PAY_HS
,COALESCE(X.MAXDATE,'31.12.9999') AS MAXDATE
FROM
GDB2DW2.IPGRP IPGRP INNER JOIN GDB2DW2.ARXIP ARXIP
ON IPGRP.IP_ID = ARXIP.IP_ID
INNER JOIN GDB2DW2.DVDCCAR DVDCCAR
ON DVDCCAR.AR_ID = ARXIP.AR_ID
LEFT JOIN GDB2DW2.DVDCSMY DVDCSMY
ON DVDCCAR.AR_ID = DVDCSMY.AR_ID

*****************from here on it gets cluggy - syntax errors...
,(SELECT MAX(PRCS_DT) AS MAXDATE
FROM GDB2DW2.DVDCSMY
WHERE PRCS_DT <= (SELECT PARM_VAL FROM GDB2DW2.LSPI001
WHERE APPL_ID = 'DI001B' AND SEQ_NUM = 5)
****************what is this orphan close-paraenthesis doing?
) X
WHERE DVDCSMY.PRCS_DT < (SELECT PARM_VAL FROM GDB2DW2.LSPI001
WHERE APPL_ID = 'DI001B' AND SEQ_NUM = 5)
WHERE APPL_ID = 'DI001B' AND SEQ_NUM = 5
************** another one:
)
AND DVDCSMY.PRCS_DT >= (SELECT PARM_VAL FROM GDB2DW2.LSPI001
WHERE APPL_ID = 'DI001B'
AND SEQ_NUM = 4)
AND DVDCSMY.PRCS_DT < IPGRP.GRP_END_DTE
AND DVDCSMY.PRCS_DT >= IPGRP.GRP_STRT_DTE
AND DVDCSMY.PRCS_DT < ARXIP.END_CST_DT
AND DVDCSMY.PRCS_DT >= ARXIP.EFF_CST_DT
AND EXISTS (SELECT PD_ID FROM GDB2DW2.DVDPRD1 DVDPRD1
WHERE DVDCCAR.PD_ID = DVDPRD1.PD_ID
AND DVDPRD1.DI_GRP_DSC =
_________________
Dick Brenholtz
American living in Varel, Germany

[kolusu]

esaccy,


The first thing you need to do learn how to write sql's that are readable and easy to understand. Here is the formatted sql without any changes and see how easy it is to read and understand

[code:1:acfbf65378]
SELECT DVDCCAR.AR_ID
,ARXIP.IP_ID
,COALESCE(DVDCSMY.PRCS_DT,'01.01.0001') AS PRCS_DT
,COALESCE(DVDCSMY.LST_STMT_BAL,0) AS LST_STMT_BAL
,COALESCE(DVDCSMY.HDYTD_NET_AMT_PRCH,0) AS HDYTD_NET_AMT_PRCH
,COALESCE(DVDCSMY.CR_LF_FL,0) AS CR_LF_FL
,COALESCE(DVDCSMY.CR_LN,0) AS CR_LN
,CAST(COALESCE(DVDCSMY.HD_PAY_HST,' ') AS CHAR(12)) AS PAY_HS
,COALESCE(X.MAXDATE,'31.12.9999') AS MAXDATE
FROM GDB2DW2.IPGRP IPGRP
INNER JOIN GDB2DW2.ARXIP ARXIP ON IPGRP.IP_ID = ARXIP.IP_ID
INNER JOIN GDB2DW2.DVDCCAR DVDCCAR ON DVDCCAR.AR_ID = ARXIP.AR_ID
LEFT JOIN GDB2DW2.DVDCSMY DVDCSMY ON DVDCCAR.AR_ID = DVDCSMY.AR_ID
,(SELECT MAX(PRCS_DT) AS MAXDATE
FROM GDB2DW2.DVDCSMY
WHERE PRCS_DT <= (SELECT PARM_VAL
FROM GDB2DW2.LSPI001
WHERE APPL_ID = 'DI001B'
AND SEQ_NUM = 5)) X
WHERE DVDCSMY.PRCS_DT < (SELECT PARM_VAL
FROM GDB2DW2.LSPI001
WHERE APPL_ID = 'DI001B'
AND SEQ_NUM = 5)
AND DVDCSMY.PRCS_DT >= (SELECT PARM_VAL
FROM GDB2DW2.LSPI001
WHERE APPL_ID = 'DI001B'
AND SEQ_NUM = 4)
AND DVDCSMY.PRCS_DT < IPGRP.GRP_END_DTE
AND DVDCSMY.PRCS_DT >= IPGRP.GRP_STRT_DTE
AND DVDCSMY.PRCS_DT < ARXIP.END_CST_DT
AND DVDCSMY.PRCS_DT >= ARXIP.EFF_CST_DT
AND EXISTS (SELECT PD_ID
FROM GDB2DW2.DVDPRD1 DVDPRD1
WHERE DVDCCAR.PD_ID = DVDPRD1.PD_ID
AND DVDPRD1.DI_GRP_DSC =

[GuyC]

Besides the duplicate line there is also invalid quotes around BF.
If you (temporarily) forget about start and end date , you get following query :