Archives of the TeradataForum
Message Posted: Fri, 08 Feb 2013 @ 20:33:41 GMT
<-- Anonymously Posted: Friday, February 08, 2013 10:52 -->
I'm unable to grasp what is happening in the below query left join query. Please note that CHS drives the left join though it is placed at the end.
If I comment out the left join(only the inner join remains0, I get back a million rows but on the inclusion of the left join at the end, I get zilch. I checked the explain too and it clearly states the intermediate spool files being joined using an 'outer join' (exact words Spool 22 and Spool 23 are right outer joined using a merge join, with condition(s) used for non-matching on right table ("NOT (DMCL_SITE_NBR IS NULL)"), with a join condition of ("DMCL_SITE_NBR = SITE_NBR").
If I reverse the sequence of the joins to, I manage to get data. What am I missing here?
SELECT CHS.CHS_BK AS EQT_BK, CHS.CHS_TYPE_CD AS EQT_TYPE_CD, ETSC.EQT_TYPE_SZ_CD AS EQT_TYPE_SZ_CD, CHS.CHS_SZ_CD AS EQT_SZ_CD, CAST(NULL AS VARCHAR(100)) AS EQT_HT_CD, CAST(NULL AS INTEGER) AS CTR_PHYSCL_CHK_DGT_NBR, CAST(NULL AS INTEGER) AS CTR_ISO_CHK_DGT_NBR, CAST(NULL AS INTEGER) AS CTR_CHK_DGT_DECAL_FLG, SITE.CITY_NBR AS DMCL_CITY_NBR, CHS.DMCL_SITE_NBR AS DMCL_SITE_NBR, CHS.EFFECTIVE_START_DTTM AS EFFECTIVE_START_DTTM, CHS.EFFECTIVE_END_DTTM AS EFFECTIVE_END_DTTM FROM FDW_BT.CHASSIS AS CHS INNER JOIN FDW_BT.EQT_TYPE_SIZE_CLASSIFICATION AS ETSC ON CHS.CHS_TYPE_CD=ETSC.EQT_TYPE_CD AND CHS.CHS_SZ_CD=ETSC.EQT_SZ_CD AND ETSC.EQT_HT_CD IS NULL AND CHS.EFFECTIVE_START_DTTM BETWEEN ETSC.EFFECTIVE_START_DTTM AND ETSC.EFFECTIVE_END_DTTM LEFT OUTER JOIN FDW_BT.SITE AS SITE ON CHS.DMCL_SITE_NBR=SITE.SITE_NBR
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|