ERROR IN CASE INSTRUCTION SQLSERVER

Question

Hello everyone,

I am all new on this forum so please forgive me if I am not using it as I should.

I have an issue on a case instruction (for a sqlserver database). When I parse the request, I have the following error message : Exception, DBD, Incorrect syntax near '".State: S0001.

And here is the case instruction :

CASE

WHEN "table1"."column1" = 'STLUB' THEN 'Lubrifiants'

WHEN "table1"."column1" = 'STPNEUS' THEN 'Pneumatiques'

WHEN "table1"."column1" <> 'STLUB' AND "table1"."column1" <> 'STPNEUS' AND "table1"."column2" = ‘Value1’ THEN ‘V1’

WHEN "table1"."column1" <> 'STLUB' AND "table1"."column1" <> 'STPNEUS' AND "table1"."column2" = ‘Value2’ THEN ‘V2’

WHEN "table1"."column1" <> 'STLUB' AND "table1"."column1" <> 'STPNEUS' AND "table1"."column2" = ‘Value3’ THEN ‘V3’

ELSE 'Autres'

END

I also tried this syntax but the error message remains the same :

CASE

WHEN "table1"."column1" = 'STLUB' THEN 'Lubrifiants'

WHEN "table1"."column1" = 'STPNEUS' THEN 'Pneumatiques'

WHEN "table1"."column1" NOT IN ('STLUB','STPNEUS') AND "table1"."column2" = ‘Value1’ THEN ‘V1’

WHEN "table1"."column1" NOT IN ('STLUB','STPNEUS') AND "table1"."column2" = ‘Value2’ THEN ‘V2’

WHEN "table1"."column1" NOT IN ('STLUB','STPNEUS') AND "table1"."column2" = ‘Value3’ THEN ‘V3’

ELSE 'Autres'

END

Can anyone help me on this issue?

Thank you very much in advance for your precious help!

Aurelie.

Answer 1

I checked the first CASE expression. It appears to be OK.

The error message is not coming from SQL Server.

>When I parse the request

Where? How are you trying to execute it?

SQL Server 2012 parsing OK in SSMS. I replaced the slanted single quotes with straight single quotes.

SELECT CASE
 
WHEN "table1"."column1" = 'STLUB' THEN 'Lubrifiants'
 
WHEN "table1"."column1" = 'STPNEUS' THEN 'Pneumatiques'
 
WHEN "table1"."column1" <> 'STLUB' AND "table1"."column1" <> 'STPNEUS' AND "table1"."column2" = 'Value1' THEN 'V1'
 
WHEN "table1"."column1" <> 'STLUB' AND "table1"."column1" <> 'STPNEUS' AND "table1"."column2" = 'Value2' THEN 'V2'
 
WHEN "table1"."column1" <> 'STLUB' AND "table1"."column1" <> 'STPNEUS' AND "table1"."column2" = 'Value3' THEN 'V3'
 
ELSE 'Autres'
 END 

---

Kalman Toth SQL 2008 GRAND SLAM
Paperback: SQL Server 2012 Pro

Answer 2

Hello Aurelie,

Welcome to the Forums !!!

The issue is the charecter used to enclose the values "Value1" & "V1" etc are not single quotes. Some times due to copy paste it happens this way. Try the below code

CASE
WHEN "table1"."column1" = 'STLUB' THEN 'Lubrifiants'
WHEN "table1"."column1" = 'STPNEUS' THEN 'Pneumatiques'
WHEN "table1"."column1" <> 'STLUB' AND "table1"."column1" <> 'STPNEUS' AND "table1"."column2" = 'Value1' THEN 'V1'
WHEN "table1"."column1" <> 'STLUB' AND "table1"."column1" <> 'STPNEUS' AND "table1"."column2" = 'Value2' THEN 'V2'
WHEN "table1"."column1" <> 'STLUB' AND "table1"."column1" <> 'STPNEUS' AND "table1"."column2" = 'Value3' THEN 'V3'
ELSE 'Autres'
END 

---

Best Regards Sorna

Answer 3

My be you are trying to execute the query without select statement. The case statement looks OK.

Try for below.

Select (Case <statement> End) as ABC

---

sarat chandra sahoo

Answer 4

Thank you very much Sorna!

I manually retype all of my ' and It worked just fine after!

Regards

Aurelie