Archives of the TeradataForum

Message Posted: Fri, 05 Apr 2013 @ 14:08:46 GMT

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

Simply adding the filtering criteria for the inner table of the outer join to have a non-null value eliminates 99%+ of the noise.

Also adding a where filter for the outer table ensures you are only going to join what is perceived valid. You need to add a UNION to the query to retrieve the "A" table rows where the BKG_BK IS NULL without any join, but accounting for the "C" table columns with NULL VALUES. The UNION will force you to account for all the resulting columns involved for both sides of the Union'ed query.

     Sel * from
     V_FACT_EQUIPMENT_ACTIVITY A
     LEFT OUTER JOIN
      UD_GEM_AD.V_SHIPMENT C
     ON A.BKG_BK = C. BKG_BK
     AND C.BKG_BK IS NOT NULL
     WHERE A.BKG_BK IS NOT NULL

     UNION

     Sel a.* ,NULL as C_COL1, NULL as C_COL2...
     From V_FACT_EQUIPMENT_ACTIVITY A
     WHERE A.BKG_BK IS NULL

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