Solved

DB2 LEFT OUTER JOIN Help needed !!!

Posted on 2004-09-30
3
764 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 18

Accepted Solution

by:
BigSchmuh earned 500 total points
ID: 12208844
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
ID: 12229663
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
ID: 12235037
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

Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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 (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

739 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