In both the cases,it is printing 'Yes' .Why??
case when 111=cast('111' as char(3)) then 'yes'
case when 111='111' then 'yes'
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.
CASE WHEN 111='111' THEN 'YES'
SELECT CASE WHEN COALESCE(111,'111')='111' THEN 'YES' ELSE 'NO' END AS X
please let me know the difference..
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.
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
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?