SQL Server- ORDER BY CASE

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

9 thoughts on “SQL Server- ORDER BY CASE

  1. user

    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
    
    Reply
  2. user

    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
    
    Reply
  3. user

    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
    
    Reply
  4. user

    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
    
    Reply
  5. user

    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.

    Reply
  6. user

    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.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *