Home Page for the TeradataForum
 

Archives of the TeradataForum

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


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


Subj:   CAST into varchar 2 versions
 
From:   Pandey, Vivek

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>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016