CASE statement in SQL

Database
KVB
Enthusiast

CASE statement in SQL

In both the cases,it is printing 'Yes' .Why??

sel

case when 111=cast('111' as char(3)) then 'yes'

else 'no'

end

sel

case when 111='111'  then 'yes'

else 'no'

end

16 REPLIES
Enthusiast

Re: CASE statement in SQL

Do you think there is any difference between these two case statements? Both when conditions will be checking '111' = '111' as TD will implicitly convert 111 to '111' for comparison.

Khurram
KVB
Enthusiast

Re: CASE statement in SQL

SELECT CASE WHEN COALESCE(111,'111')='111' THEN 'YES' ELSE 'NO' END AS X

it is printing NO..What's the difference??

KVB
Enthusiast

Re: CASE statement in SQL

SEL

CASE WHEN 111='111'  THEN 'YES'

ELSE 'NO'

END

 Printing YES

SELECT CASE WHEN COALESCE(111,'111')='111' THEN 'YES' ELSE 'NO' END AS X

Printing NO

please let me know the difference..

Enthusiast

Re: CASE statement in SQL

COALESCE is a short form of CASE, in your case it will be executed as

CASE WHEN 111 IS NULL THEN '111' ELSE 111 END

So the else part is returning Integer explicitly, and it is not equal to '111' which is a character data type. But in simple 111='111' Teradata converts the integer type to character data type implicilty, so it returns Y.

Khurram
KVB
Enthusiast

Re: CASE statement in SQL

Then how to write this case to print YES for the second statement .SELECT CASE WHEN COALESCE(111,'111')='111' THEN 'YES' ELSE 'NO' END AS X

KVB
Enthusiast

Re: CASE statement in SQL

What about the below statement

SELECT CASE WHEN COALESCE(111,111)='111' THEN 'YES' ELSE 'NO' END AS X

It should also return NO.

Enthusiast

Re: CASE statement in SQL

what os the type of data you have in the column? like you are writing constant 111, do you have a character data column or Integer? 

Khurram
KVB
Enthusiast

Re: CASE statement in SQL

COALESCE(ID,'111')<>'123456'  where ID is the INTEGER.

Enthusiast

Re: CASE statement in SQL

Is there any specific reason for involving Character data here? Why dont you use:

COALESCE(ID,111)<> 123456
Khurram