CODEWITHSUNDEEP
sqlsql-server
Patcouch22
Patcouch22

Reputation: 912

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

Upvotes: 0

Views: 1868

Answers (6)

Sean
Sean

Reputation: 492

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.

Upvotes: 0

Stefan Steinegger
Stefan Steinegger

Reputation: 64648

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.

Upvotes: 1

SQLMenace
SQLMenace

Reputation: 135151

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

Upvotes: 1

squillman
squillman

Reputation: 13641

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

Upvotes: 0

KM.
KM.

Reputation: 103697

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

Upvotes: 6

Adam Robinson
Adam Robinson

Reputation: 185693

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

Upvotes: 0

Related Questions