All Forums Database
KVB 124 posts Joined 09/12
03 Oct 2013
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

M.Saeed Khurram 544 posts Joined 09/12
03 Oct 2013

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 124 posts Joined 09/12
03 Oct 2013

SELECT CASE WHEN COALESCE(111,'111')='111' THEN 'YES' ELSE 'NO' END AS X
 
it is printing NO..What's the difference??

KVB 124 posts Joined 09/12
03 Oct 2013

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..

M.Saeed Khurram 544 posts Joined 09/12
03 Oct 2013

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 124 posts Joined 09/12
03 Oct 2013

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 124 posts Joined 09/12
03 Oct 2013

What about the below statement
SELECT CASE WHEN COALESCE(111,111)='111' THEN 'YES' ELSE 'NO' END AS X
It should also return NO.

M.Saeed Khurram 544 posts Joined 09/12
03 Oct 2013

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 124 posts Joined 09/12
03 Oct 2013

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

M.Saeed Khurram 544 posts Joined 09/12
03 Oct 2013

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

COALESCE(ID,111)<> 123456

 

Khurram

ulrich 816 posts Joined 09/09
03 Oct 2013

try to avoid implicit conversions. 
 
If you compare with a char constant on the righ side make sure the left side is also char.
something like coalesce(trim(cast(id as varchar(11))),'111') <> '123456'
I am not 100% sure where the issue is but the case seems to result in unicode so there will be more then one conversion needed. 
 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

KVB 124 posts Joined 09/12
03 Oct 2013

Yes.Thank You!!

M.Saeed Khurram 544 posts Joined 09/12
03 Oct 2013

Ulrich, 
You are very right, I have checked the type and the converted result is unicode, and it is creating the mess.
 

Khurram

dnoeth 4628 posts Joined 11/04
03 Oct 2013

If two columns with different datatypes are compared there will be an automatic typecast to get comparable datatypes.
When a string and a numeric colum are compared the string will be converted to a FLOAT and not the numeric to a string.
There's a simple reason for this: a numeric value like 1 can be represented by many different strings, e.g. '1', '1.0', ' 1', etc. 
When a numeric is automatically casted it's always a Teradata style cast which uses the FORMAT of the numeric column and results in a right alogned string with leading blanks.

SELECT COALESCE(111,'111') AS x, TYPE(x), FORMAT(111);

x    Type(x)                                 Format(111)
---- --------------------------------------- -----------
 111 VARCHAR(4) CHARACTER SET UNICODE        -(3)9
SELECT CASE WHEN COALESCE(111,'111')='111' THEN 'YES' ELSE 'NO' END AS X

returns 'No' because the it results in ' 111' = '111' which is definitely false.
 
Dieter

Dieter

frozenshine 7 posts Joined 03/15
21 Mar 2016

Hi,
My table has data: C1, jan, 23 || C1, feb, 55 || C3, march, 100 .
 
I am joining it with another table and want it to be like:
||||col1 ||janPayment|| febpayment|| marchpayment||||
||||C1 || 23 || 55 || 100 ||.
 
The Case statement give me 3 rows for usage in months rather then 1 and b1 data is same in all 3 rows:
SEL b1.*,CASE WHEN ub.MONTH_NAME ='January' THEN UsageMB END AS UsageJan,      
CASE WHEN ub.MONTH_NAME ='February' THEN UsageMB  END AS UsagFeb, 
CASE WHEN ub.MONTH_NAME ='March' THEN UsageMB END AS UsageMarch
FROM 
PDP_TMP.Data_BB1 b1
LEFT JOIN
PDP_TMP.Usage_BB ub ON b1.SBSCRP_ID = ub.SBSCRP_ID

ank

Kumar Rayapati 1 post Joined 01/16
28 Mar 2016
SEL CASE WHEN COALESCE('111','111')='111' THEN 'YES' ELSE 'NO' END  AS X
SEL CASE WHEN COALESCE (111,'111')=111 THEN 'YES' ELSE 'NO' END AS 

The above  queries will give exact result you are looking for , coz when integer value is put in quotes, it will be treated as literal and varchar datatype. thats why it  is giving 'NO' instead of 'yes' .
 

Ekladios 13 posts Joined 02/12
24 Jun 2016

CASE Statement:
If any WHERE condition is met then it will ignore ELSE. But this is not the case my example
 
This works fine
SELECT                  'DEP_INT_CTL', 'Job_Control' , 'Run_Time' ,  
CASE     'AT'  
                WHEN 'AT' THEN 8    
                WHEN 'DA' THEN 10    
                WHEN 'DH' THEN 4    
                WHEN 'DM' THEN 6    
                WHEN 'DS' THEN 14    
                WHEN 'HM' THEN 8    
                WHEN 'HS' THEN 16    
                WHEN 'SZ' THEN 25    
                WHEN 'TS' THEN 19   
                WHEN 'TZ' THEN 25    
                WHEN 'YM' THEN 19   
                 ELSE '100'
         END AS COL_SZ 
FROM    DEP_INT_CTL.Job_Control
GROUP BY 1  , 2 ;                                                  Result is  8
 
This one does not work??
 
 
 
SELECT     'DEP_INT_CTL', 'Job_Control' , 'Run_Time' ,  
CASE     'AT'  
                WHEN 'AT' THEN 8    
                WHEN 'DA' THEN 10    
                WHEN 'DH' THEN 4    
                WHEN 'DM' THEN 6    
                WHEN 'DS' THEN 14    
                WHEN 'HM' THEN 8    
                WHEN 'HS' THEN 16    
                WHEN 'SZ' THEN 25    
                WHEN 'TS' THEN 19   
                WHEN 'TZ' THEN 25    
                WHEN 'YM' THEN 19   
    ELSE  COALESCE ( MAX ( CHAR ( TRIM ( Run_Time) ) ) , 0 ) 
         END AS COL_SZ 
FROM    DEP_INT_CTL.Job_Control
GROUP BY 1  , 2 ;
 Fails 5407 Invalid operation for Date Time or interval
 
 
Can someone explain please.
 

You must sign in to leave a comment.