SQL Server- ORDER BY CASE problem

Question

I have the following the query running dynamically

SELECT *
FROM Vehicles
WHERE (DKID IN (69954))
ORDER BY case when ImageName1 = 'na' then 0 else 1 end, Make , Model, Year DESC

This is returning the following error:

Error Executing Database Query. [Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near 'na'.

Thanks in advanced for your help

Answer 1

Try enclosing the case statement in parentheses.

SELECT *
FROM Vehicles
WHERE (DKID IN (69954))
ORDER BY 
    (case when ImageName1 = 'na' then 0 else 1 end), Make , Model, Year DESC

Answer 2

works for me

here is repo script

use tempdb
go

create table Vehicles(DKID int,ImageName1 varchar(50),
                          Make int, Model int, Year int)

insert Vehicles values (69954,'na',1,1,2007)
insert Vehicles values(69954,'bla',1,1,2008)
go

SELECT *
FROM Vehicles
WHERE (DKID IN (69954))
ORDER BY case when ImageName1 = 'na' then 0 else 1 end, 
Make , Model, Year DESC

Answer 3

are you running this query dynamically?, if so you might need to escape the quotes around 'na':

SELECT *
FROM Vehicles
WHERE (DKID IN (69954))
ORDER BY case when ImageName1 = ''na'' then 0 else 1 end, Make , Model, Year DESC

Answer 4

Your query works fine for me in SQL Mgmt Studio... Maybe try it this way instead to see if it gets you anywhere:

SELECT
    case when ImageName1 = 'na' then 0 else 1 end as OrderCol,
    *
FROM Vehicles
WHERE (DKID IN (69954))
ORDER BY OrderCol,Make,Model,Year DESC

Answer 5

You're using JDBC. Is there probably a transformation / interpretation from JDBC? Try making the 'na' a parameter. Check if there is a certain syntax in JDBC for string constants in queries. I don't use JDBC, so I could be completely wrong.

Answer 6

As KMike said, it looks like you didn't not escape properly.

Basically, when you ran your statement, it did not generate a syntactically correct SQL statement from the dynamic SQL.

Generally, when I am writing dynamic sql, I use a print statement to print the generated SQL. I can then review the generated sql visually for obvious mistakes, and then execute it it to make sure it works as expected.

If I made a mistake in the dynamic SQL, it will usually be revealed here.