TABLES JOINING on a ref table

Database
N/A

TABLES JOINING on a ref table

Format:HTML Format Version:1.0 StartHTML: 165 EndHTML: 17150 StartFragment: 314 EndFragment: 17118 StartSelection: 314 EndSelection: 314SyntaxEditor Code Snippet

From my table LAB_T_LOADDATA.Conditions_DB_Attribute_Mapping_20160912_2 I would like values such as T730 and T730A to be returned. In the table LAB_T_LOADDATA.CONDITIONS_RAW_TABS_201608 the reference tables values do not have a Prefix 'T' or  Suffix 'A', though the table LAB_T_LOADDATA.Conditions_DB_Attribute_Mapping_20160912_2 does have these . So how do I join these 2 tables so I can return the Codes T730 aand T730A in my result set?. I am trying the following join:

Select
Record_Type
,TABS.ACCNT,ATT.COND_CODE AS "TABS_COND_CODE" FROM Lab_T_RPTData.CBI_01_POPID_union_table accts
LEFT JOIN LAB_T_LOADDATA.CONDITIONS_RAW_TABS_201608 TABS ON accts.Accnt=TABS.LOAN_NO LEFT JOIN LAB_T_LOADDATA.Conditions_DB_Attribute_Mapping_20160912_2 att ON 'T'||TABS.COND_CODE=att.COND_CODE

LEFT
JOIN                     LAB_T_LOADDATA.Conditions_DB_Attribute_Mapping_20160912_2 att
ON                                    'T'||TABS.COND_CODE||'A'=att.COND_CODE
 
 
 
 

 

1 REPLY
N/A

Re: TABLES JOINING on a ref table

 What's the data type of TABS.COND_CODE?

If it's CHAR you might have trailing spaces and if it's INT the 'T'||TABS.COND_CODE||'A' will results in an automatic type cast which will add leading spaces.

 

Try 'T'|| TRIM(TABS.COND_CODE) ||'A'