Archives of the TeradataForum

Message Posted: Fri, 30 Jun 2006 @ 08:59:45 GMT


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

Subj:		Re: CAST into varchar 2 versions

From:		Dieter Noeth

Vivek Pandey wrote:

I was thinking why one SQL works while the other doesn't? I even tried doing the TRIM after converting the data type (as you suggested)

but that still doesn't explains the behavior. I pasted the explains as I saw some difference in the optimizer plan in the two cases, however that doesn't explains why one case is working while the other is failing!

Both versions actually *do* a typecast, but use different rules for it. The manuals are correct, they even talk about those differences:

SQL Reference: Functions and Operators

Chapter 15: Data Type Conversions

Numeric-to-Character Conversion

How CAST Differs from Teradata Conversion Syntax

In short: Both use the column's FORMAT, but Teradata style casts right-aligned, whereas ANSI casts left-aligned.

     sel cast(1234567 as char(11)) || ';',
          (1234567 (char(11))) || ';',
          (1234567 (char(20))) || ';',
          (1234567 (char(5))) || ';';

       *** Query completed. One row found. 3 columns returned.
       *** Total elapsed time was 1 second.

     (1234567||';')  (1234567||';')  (1234567||';')
     --------------  --------------  ---------------------
     1234567    ;        1234567;        1234567         ;

There are lots of possible ways to represent 1 in a string:

     '1', ' 1', '1 ', '1.0', '1.00', ...

That's why it's recommended to cast the char to a numeric value :-)

Btw, if you really join int to varchar, then your datamodel is flawed...

Dieter


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

Attachments

Library

Quick Reference