Solved

DB2 LEFT OUTER JOIN Help needed !!!

Posted on 2004-09-30
3
761 Views
Last Modified: 2008-02-01
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 !!!

0
Comment
Question by:ranjanprava
  • 2
3 Comments
 
LVL 18

Accepted Solution

by:
BigSchmuh earned 500 total points
Comment Utility
You just added 2 left joins to VTEC_EMPE and 1 to VCODETBL_DATA and I can insure you this has nothing to do with any values of the fields from VISSUE.
==> You can add as many LEFT JOIN as you wish, they will not empty other tables fields but can return their own fields empty

If your new tables (aliased S2 S3 and T7) does not returns rows, it can be:
a) Fields from VISSUE are already empty (CRTD_USER_ID, UPDT_USER_ID and ISSUE_STS_CD) which means your LEFT JOIN returns NULL for every new fields
b) VISSUE is empty at all (as it comes from another LEFT JOIN from VREVIEW)
c) Rows exist but your calculated value "RTRIM(S2.LAST_NM) CONCAT ',' CONCAT ' ' CONCAT RTRIM(S2.FIRST_NM)" will return NULL if any field is NULL (means if FIRST_NM is empty the full expr is although empty)
==> Can you check for S2, S3, T7 row existence and check the FIRST_NM, LAST_NM fields alone from S2 and S3 ?
0
 

Author Comment

by:ranjanprava
Comment Utility
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
0
 
LVL 18

Expert Comment

by:BigSchmuh
Comment Utility
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/
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now