Archives of the TeradataForum

Message Posted: Wed, 28 Jun 2006 @ 18:43:10 GMT

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

Hi,

In the below 2 SQLs tt1.a is integer and t1.a is a char column. The amazing part is first one (in which explain tells that trimming as well is happening) returns the records while the second one fails to do so.

The question is why the 2nd type of casting doesn't do the trimming while the first syntax does?

     sel *
     From tt1,t1
     Where cast(tt1.a As varchar(10)) =t1.a


       5) We do an all-AMPs JOIN step from VIV.t1 by way of a RowHash
          match scan with no residual conditions, which is joined to Spool 2
          (Last Use) by way of a RowHash match scan.  VIV.t1 and Spool
          2 are joined using a merge join, with a join condition of (
          "(TRIM(BOTH FROM {RightTable}.a (VARCHAR(10), CHARACTER SET LATIN,
          NOT CASESPECIFIC, FORMAT '-(10)9'))(VARCHAR(10), CHARACTER SET
          LATIN, NOT CASESPECIFIC))= VIV.t1.a").  The result goes into
          Spool 1 (group_amps), which is built locally on the AMPs.  The
          size of Spool 1 is estimated with index join confidence to be
          7,744 rows.  The estimated time for this step is 0.03 seconds.


     sel *
     From tt1,t1
     Where tt1.a (varchar(10)) =t1.a

       5) We do an all-AMPs JOIN step from VIV.t1 by way of a RowHash
          match scan with no residual conditions, which is joined to Spool 2
          (Last Use) by way of a RowHash match scan.  VIV.t1 and Spool
          2 are joined using a merge join, with a join condition of ("(a
          (VARCHAR(10), CHARACTER SET LATIN, NOT CASESPECIFIC, FORMAT
          '-(10)9')(VARCHAR(10), CHARACTER SET LATIN, NOT CASESPECIFIC))=
          VIV.t1.a").  The result goes into Spool 1 (group_amps), which
          is built locally on the AMPs.  The size of Spool 1 is estimated
          with index join confidence to be 7,744 rows.  The estimated time
          for this step is 0.03 seconds.

Thanks, Vivek

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