CASE statement in SQL

Database
Senior Supporter

Re: CASE statement in SQL

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. 

KVB
Enthusiast

Re: CASE statement in SQL

Yes.Thank You!!

Junior Supporter

Re: CASE statement in SQL

Ulrich, 

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

Khurram
Ambassador

Re: CASE statement in SQL

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

Enthusiast

Re: CASE statement in SQL

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

Not applicable

Re: CASE statement in SQL

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

Highlighted
Enthusiast

Re: CASE statement in SQL

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.