ORDER BY CASE WHEN

Question

User-1630378091 posted

I am trying to use a ORDER BY CASE to order the output.

I have used  the followinng staement and it works fine;

"SELECT tblStock.* FROM tblStock WHERE ID_No <> " & lngRecordNo & " AND tblstock.price <> 'SOLD' ORDER BY tblStock.DOR DESC"

But when I add the ORDER BY CASE like so;

"SELECT tblStock.* FROM tblStock WHERE ID_No <> " & lngRecordNo & " ORDER BY CASE WHEN tblstock.price = 'SOLD' THEN 1 ELSE 0 END, tblStock.DOR DESC"

it throws an IErrorInfo error, what am I doing wrong?

Answer 1

User-125547262 posted

The case statement is the issue

"SELECT tblStock.*, CASE WHEN tblstock.price = 'SOLD' THEN 1 ELSE 0 END FROM tblStock WHERE ID_No <> " & lngRecordNo & "

ORDER BY  tblStock.DOR DESC

Answer 2

User-1630378091 posted

I tried thhat but got the same error. Here is my full connection code;

    Dim lngRecordNo As String
    lngRecordNo = Request.QueryString("ID")
    dim dbconn,sql,dbcomm,dbread
    dbconn=New OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA Source=path/file.mdb")
    dbconn.Open()
    sql="SELECT tblStock.*, CASE WHEN tblstock.price = 'SOLD' THEN 1 ELSE 0 END FROM tblStock WHERE ID_No <> " & lngRecordNo & " ORDER BY  tblStock.DOR DESC"
    dbcomm=New OleDbCommand(sql,dbconn)
    dbread=dbcomm.ExecuteReader()
   

Answer 3

User-1199946673 posted

Assuming you're using Access (since this is posted in the Access Databases and AccessDataSource Control forum) , the problem is the CASS WHEN statement. In Access, you should use the IIF function:

IIF(tblstock.price = 'Sold', 1, 0)

Also, you better start using parameters:

http://www.mikesdotnetting.com/Article/26/Parameter-Queries-in-ASP.NET-with-MS-Access

Answer 4

User-1630378091 posted

Thank you hans, works fine now I replaced the CASE with an IIF.