SQL Server- ORDER BY CASE problem

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

From stackoverflow

• 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
  

• 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
  

• 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
  

  Patcouch22 : I am running it dynamically, however the escape quotes still provide the same error.

  KM : try replacing 'na' with char(110)+char(97), which will be slow, but will help diagnose it more...

  Patcouch22 : that did allow it to run...

  KM : that shows that you are not escaping it properly, is "na" a constant or part of the dynamic query? edit your question to show that is generating the query, the propblem is there.

• 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
  

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

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