asked on

DB2 LEFT OUTER JOIN Help needed !!!

Hello ,
I am having problem in left outer join in DB2 Version 7 database. Basically I have a query which is like this

SELECT R1.CNTRCT_ID, R1.AMNDMT_ID, R1.REVIEW_ID,
R1.ASGND_BY_USER_ID, T1.ATTR1_TX AS ASGND_BY_USER_NM,
R1.ASGND_TO_USER_ID, T2.ATTR1_TX AS ASGND_TO_USER_NM,
R1.CLOSD_FL, R1.CMPLTN_BY_ID, T3.ATTR1_TX AS CMPLTN_BY_NM,
R1.CMPLTN_DT, R1.DAY_LEFT_QY, R1.DUE_DT, R1.FRST_ASGND_DT,
R1.RCVD_DT, R1.REVIEW_AREA_CD, T4.ATTR1_TX AS REVIEW_AREA_TX,
R1.REVIEW_CMNT_TX, R1.REVIEW_ORDER_TX, R1.REVIEW_TYPE_CD,
T5.ATTR1_TX AS REVIEW_TYPE_TX, R1.WRKFL_STS_CD, T6.ATTR1_TX AS
WRKFL_STS_TX, S1.ISSUE_DESC_TX,S1.ISSUE_DT, S1.ISSUE_RSLTN_TX,
S1.ISSUE_STS_CD, S1.RSLTN_DT, S1.RSLTN_STS_CD,S1.ISSUE_ID
FROM CATS.VREVIEW R1 LEFT OUTER JOIN CATS.VISSUE S1
ON R1.CNTRCT_ID = S1.CNTRCT_ID AND
R1.AMNDMT_ID = S1.AMNDMT_ID AND
R1.REVIEW_ID = S1.REVIEW_ID
LEFT OUTER JOIN CATS.VCODETBL_DATA AS T1
ON R1.ASGND_BY_USER_ID = T1.ENCODE1_CD AND
T1.CODETBL_NM = 'REVNAME' AND
T1.ATTR2_TX<>'T' AND
T1.BSNS_SYS_CD = 'TS'
LEFT OUTER JOIN CATS.VCODETBL_DATA AS T2
ON R1.ASGND_TO_USER_ID = T2.ENCODE1_CD AND
T2.CODETBL_NM = 'REVNAME' AND
T2.ATTR2_TX<>'T' AND
T2.BSNS_SYS_CD = 'TS'
LEFT OUTER JOIN CATS.VCODETBL_DATA AS T3
ON R1.CMPLTN_BY_ID = T3.ENCODE1_CD AND
T3.CODETBL_NM = 'REVNAME' AND
T3.ATTR2_TX<>'T' AND
T3.BSNS_SYS_CD = 'TS'
LEFT OUTER JOIN CATS.VCODETBL_DATA AS T4
ON R1.REVIEW_AREA_CD = T4.ENCODE1_CD AND
T4.CODETBL_NM = 'REVAREA' AND
T4.BSNS_SYS_CD = 'TS'
LEFT OUTER JOIN CATS.VCODETBL_DATA AS T5
ON R1.REVIEW_TYPE_CD = T5.ENCODE1_CD AND
T5.CODETBL_NM = 'REVTYPE' AND
T5.BSNS_SYS_CD = 'TS'
LEFT OUTER JOIN CATS.VCODETBL_DATA AS T6
ON R1.WRKFL_STS_CD = T6.ENCODE1_CD AND
T6.CODETBL_NM = 'REVSTS' AND
T6.BSNS_SYS_CD = 'TS'
WHERE
R1.CNTRCT_ID <> '3200201377' AND
R1.REVIEW_TYPE_CD <> ' ';

Now I need to get more fields from the table VISSUE.

I added three new joins and the query is like this

SELECT R1.CNTRCT_ID, R1.AMNDMT_ID, R1.REVIEW_ID,
R1.ASGND_BY_USER_ID, T1.ATTR1_TX AS ASGND_BY_USER_NM,
R1.ASGND_TO_USER_ID, T2.ATTR1_TX AS ASGND_TO_USER_NM,
R1.CLOSD_FL, R1.CMPLTN_BY_ID, T3.ATTR1_TX AS CMPLTN_BY_NM,
R1.CMPLTN_DT, R1.DAY_LEFT_QY, R1.DUE_DT, R1.FRST_ASGND_DT,
R1.RCVD_DT, R1.REVIEW_AREA_CD, T4.ATTR1_TX AS REVIEW_AREA_TX,
R1.REVIEW_CMNT_TX, R1.REVIEW_ORDER_TX, R1.REVIEW_TYPE_CD,
T5.ATTR1_TX AS REVIEW_TYPE_TX, R1.WRKFL_STS_CD, T6.ATTR1_TX AS
WRKFL_STS_TX, S1.ISSUE_DESC_TX,S1.ISSUE_DT, S1.ISSUE_RSLTN_TX,
S1.RSLTN_DT, S1.RSLTN_STS_CD,S1.ISSUE_ID,
S1.CRTD_USER_ID,S1.UPDT_USER_ID,T7.ATTR1_TX AS ISSUE_STS_TX,
S1.ISSUE_STS_CD, S1.ISSUE_DESC_TX,
S2.FIRST_NM, S2.LAST_NM,S3.FIRST_NM, S3.LAST_NM,
RTRIM(S2.LAST_NM) CONCAT ',' CONCAT ' ' CONCAT RTRIM(S2.FIRST_NM)
AS ISSUE_CRTD_USER_NM,
RTRIM(S3.LAST_NM) CONCAT ',' CONCAT ' ' CONCAT RTRIM(S3.FIRST_NM)
AS ISSUE_UPDT_USER_NM

FROM CATS.VREVIEW R1 LEFT OUTER JOIN CATS.VISSUE S1
ON R1.CNTRCT_ID = S1.CNTRCT_ID AND
R1.AMNDMT_ID = S1.AMNDMT_ID AND
R1.REVIEW_ID = S1.REVIEW_ID
LEFT OUTER JOIN CATS.VCODETBL_DATA AS T1
ON R1.ASGND_BY_USER_ID = T1.ENCODE1_CD AND
T1.CODETBL_NM = 'REVNAME' AND
T1.ATTR2_TX<>'T' AND
T1.BSNS_SYS_CD = 'TS'
LEFT OUTER JOIN CATS.VCODETBL_DATA AS T2
ON R1.ASGND_TO_USER_ID = T2.ENCODE1_CD AND
T2.CODETBL_NM = 'REVNAME' AND
T2.ATTR2_TX<>'T' AND
T2.BSNS_SYS_CD = 'TS'
LEFT OUTER JOIN CATS.VCODETBL_DATA AS T3
ON R1.CMPLTN_BY_ID = T3.ENCODE1_CD AND
T3.CODETBL_NM = 'REVNAME' AND
T3.ATTR2_TX<>'T' AND
T3.BSNS_SYS_CD = 'TS'
LEFT OUTER JOIN CATS.VCODETBL_DATA AS T4
ON R1.REVIEW_AREA_CD = T4.ENCODE1_CD AND
T4.CODETBL_NM = 'REVAREA' AND
T4.BSNS_SYS_CD = 'TS'
LEFT OUTER JOIN CATS.VCODETBL_DATA AS T5
ON R1.REVIEW_TYPE_CD = T5.ENCODE1_CD AND
T5.CODETBL_NM = 'REVTYPE' AND
T5.BSNS_SYS_CD = 'TS'
LEFT OUTER JOIN CATS.VCODETBL_DATA AS T6
ON R1.WRKFL_STS_CD = T6.ENCODE1_CD AND
T6.CODETBL_NM = 'REVSTS' AND
T6.BSNS_SYS_CD = 'TS'
LEFT OUTER JOIN CATS.VTEC_EMPE AS S2
ON S1.CRTD_USER_ID = S2.LOGON_ID
LEFT OUTER JOIN CATS.VTEC_EMPE AS S3
ON S1.UPDT_USER_ID = S3.LOGON_ID
LEFT OUTER JOIN CATS.VCODETBL_DATA AS T7
ON S1.ISSUE_STS_CD = T7.ENCODE1_CD AND
T7.CODETBL_NM = 'RSLTNSTS'

WHERE
R1.CNTRCT_ID <> '3200201377' AND
R1.REVIEW_TYPE_CD <> ' ';

But the new fields from VISSUE is not getting populated . Please help !!!

I need this asap. And since last one week I am stuck !!!

ASKER CERTIFIED SOLUTION

BigSchmuh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ranjanprava

ASKER

Thank you very much for replying , could you explain how do I check for row existence ? Should I add IFNULL in front of the column. Also when I run this query in development environment I see coloumns are populated but innproduction all the columns from ISSUE tables are empty.

Anyway Thanks for replying to my query

BigSchmuh

You can use CASE expression to return different expressions depending on your criterias, you although have the COALESCE function to specific NULLable columns.
CASE WHEN condition-expr THEN value-expr ELSE value_if_not_condition-expr END
==> Means you can return almost every expression whatever the conditions are
COALESCE(NullableColumn1, NullableColumn2, NonNullStaticValueToAvoidNullResultInThisColumn)
==> It returns the first non Null value in the list

Bonus: The freeware below (I don't even know the guy) is the best you can imagine for DB2
http://chuzhoi_files.tripod.com/