Archives of the TeradataForum

Message Posted: Fri, 08 Feb 2013 @ 20:33:41 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

<-- Anonymously Posted: Friday, February 08, 2013 10:52 -->

Hi All,

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

	<Prev	Next>		<<First	<Prev	Next>	Last>>