SQL multiple Case when statement

Question

I am trying to execute the below query.

SELECT
ABL.Address1 AS BillingAddress1,
ABL.Address2 AS BillingAddress2,
ABL.Address3 AS BillingAddress3,
ABL.Address4 AS BillingAddress4,
ABL.UnitNumber AS BillingUnitNumber,
ABL.City AS BillingCity,
ABL.ProvinceOrState AS BillingProvinceOrState,
ABL.PostalCode AS BillingPostalCode,
CASE ISNULL (ABL.CountryCode,'')
                                WHEN 'CAN' THEN 'CANADA'
                                WHEN 'CA' THEN 'CANADA'
                                WHEN 'US' THEN 'USA'    
                                WHEN '' THEN
CASE WHEN CHARINDEX(RTRIM(ABL.ProvinceOrState),'BC,NWT,SK,MB,ON,QC,NB,NS,PEI,NF')&gt;0                                                                                THEN 'CANADA'                                                                    ELSE NULL                                                                              END
ELSE ABL.CountryCode                
END AS BillingCountryCode

but it throwing the below error.

Msg 102, Level 15, State 1, Line 85 Incorrect syntax near ';'.

I am unable to find whats wrong with the query.

Did you try it yourself in `SSMS`? If you click on the error message, `SSMS` will easily show you the invalid character in your 3rd line from end! — Md. Suman Kabir, Nov 08 '17 at 05:37

CristiC · Answer 1 · 2017-11-09T07:50:14.647

2

You have the encoded > instead of >:

SELECT 
ABL.Address1 AS BillingAddress1,
ABL.Address2 AS BillingAddress2,
ABL.Address3 AS BillingAddress3,
ABL.Address4 AS BillingAddress4,
ABL.UnitNumber AS BillingUnitNumber,
ABL.City AS BillingCity,
ABL.ProvinceOrState AS BillingProvinceOrState,
ABL.PostalCode AS BillingPostalCode,
CASE ISNULL (ABL.CountryCode,'')
                                WHEN 'CAN' THEN 'CANADA'
                                WHEN 'CA' THEN 'CANADA'
                                WHEN 'US' THEN 'USA'    
                                WHEN '' THEN
CASE WHEN CHARINDEX(RTRIM(ABL.ProvinceOrState),'BC,NWT,SK,MB,ON,QC,NB,NS,PEI,NF') > 0  
THEN 'CANADA'
ELSE NULL
END
ELSE ABL.CountryCode                
END AS BillingCountryCode

edited Nov 09 '17 at 07:50

answered Nov 08 '17 at 05:28

CristiC

22,068
12
57
89

Thank you. I am also getting error on the same query as below. – unnikrishnan Nov 08 '17 at 05:54
Statement has a lack of `THEN` sections :) – Ivan Starostin Nov 08 '17 at 07:39
@IvanStarostin - nope, it was on the same line, far to the right :). I have edited the answer, placing them on new lines. – CristiC Nov 09 '17 at 07:51

score 1 · Answer 2 · answered Nov 08 '17 at 05:30

I see there is a text &gt ; in the case statement instead of >

SELECT
ABL.Address1 AS BillingAddress1,
ABL.Address2 AS BillingAddress2,
ABL.Address3 AS BillingAddress3,
ABL.Address4 AS BillingAddress4,
ABL.UnitNumber AS BillingUnitNumber,
ABL.City AS BillingCity,
ABL.ProvinceOrState AS BillingProvinceOrState,
ABL.PostalCode AS BillingPostalCode,
CASE ISNULL (ABL.CountryCode,'')
                                WHEN 'CAN' THEN 'CANADA'
                                WHEN 'CA' THEN 'CANADA'
                                WHEN 'US' THEN 'USA'    
                                WHEN '' THEN
CASE WHEN CHARINDEX(RTRIM(ABL.ProvinceOrState),'BC,NWT,SK,MB,ON,QC,NB,NS,PEI,NF') > 0                                                                                THEN 'CANADA'                                                                    ELSE NULL                                                                              END
ELSE ABL.CountryCode                
END AS BillingCountryCode

Try This one - I just replaced the string "&gt ;" with > symbol

This might have happened when you copied the code form any other window or text editor

SQL multiple Case when statement

2 Answers2