Archives of the TeradataForum

Message Posted: Wed, 01 Mar 2006 @ 15:00:38 GMT

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

Srini is right about this. Coalesce() works fine in the select list, but not so well in a join condition.

     INNER JOIN DATA_SOURCES_FILTER B
     ON COALESCE(TRIM(TGT.DATA_SOURCE_CD),'') =
     COALESCE(TRIM(B.DATA_SOURCE_CD),'' )

If we assume that data_source_cd is never = '', we can remove the coalesce by recoding as follows:

     ON (TGT.DATA_SOURCE_CD = B.DATA_SOURCE_CD)
        or (TGT.DATA_SOURCE_CD IS NULL AND B.DATA_SOURCE_CD IS NULL)

But that reveals another problem. If I'm not mistaken, that will produce a Cartesian product between all rows on TGT and B that have nulls -- hence the estimate of 200+ million rows.

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