All Forums Database
RGlass 35 posts Joined 09/04
11 Dec 2007
Incorrect answerset.

When selecting from table where col1 is = 0records where col1 is = ' ' are being returned. Column is Char(1)Is this normal?create table dsb.zero_null(col1 char(1),col2 smallint,col3 smallint)primary index(col1,col2,col3);insert into dsb.zero_nullvalues(,1,2);insert into dsb.zero_nullvalues('0',3,4);insert into dsb.zero_nullvalues(0,5,6);insert into dsb.zero_nullvalues(' ',7,8);insert into dsb.zero_nullvalues('',9,10);sel * from dsb.zero_null;col1 col2 col3? 1 20 3 4 5 6 7 8 9 10*********************sel * from dsb.zero_nullwhere col1 = 0; col1 col2 col3 9 100 3 4 5 6 7 8*********************select * from dsb.zero_nullwhere col1 = '0'col1 col2 col30 3 4Thanks,R.Glass

Jim Chapman 449 posts Joined 09/04
11 Dec 2007

You might want to read the topic "Implicit Type Conversion of Character Operands" in the Teradata SQL reference manual. When comparing a numeric value to a character value, both operands are first converted to FLOAT. In the conversion from character to numeric, leading blanks in the character value are interpreted as 0.

You must sign in to leave a comment.