problems with case statement in where clause

[shlo]

I was wondering if someone would be able to tell me what is wrong with my code here:

SELECT COUNT(*)
FROM CALL
WHERE CALL.DESCRIPTIO N =
CASE
WHEN @Param1 = 'In' THEN 'In'
WHEN @Param1 = 'Out' THEN 'Out'
END

I am getting an error that parameter is correct.

And just in case there is any confusion, I am taking in 1 parameter that is either going to be 'In' or 'Out'. Depending on the param, I want to set Call.Descriptio n to either 'In' or 'Out'.

Thanks in advance!

[ck9663]

I believe the columns on the ORDER BY clause must be on your dataset or use ordinal position.

-- CK

[ShadowTech]

I'm testing this in SQL Server 2005, Management Studio.

Correct me if I'm wrong, but when you're just running a query in SS, you can just type the query, press 'Execute' and enter the parameters at that time, no? I'm confused because when I have a query with parameters in the SELECT or WHERE clause, it works perfectly fine.

SELECT     *
FROM CALL
WHERE CALL.DESCRIPTION =  @Param1

However, when I have the parameter in the ORDER BY, I'm being asked to define the variable. The exact error message: Msg 137, Level 15, State 2, Line 3
Must declare the scalar variable "@orderBy".

SELECT START_DATE, CALL_ID
FROM CALL
ORDER BY CASE WHEN @orderBy = 'A' THEN START_DATE, CALL_ID END

I guess the real question is this second bit of code correct? If so, what is causing the error message of 'Must declare the scalar variable 'OrderBy''? All the examples I've seen only show dynamic ORDER BY in stored procedures, so I don't know.

Thanks again.

Try declaring the variable

Declare @orderBy char(1)
Set @orderBy = 'A'
SELECT START_DATE, CALL_ID
FROM CALL
ORDER BY CASE WHEN @orderBy = 'A' THEN START_DATE, CALL_ID END

[shlo]

Try declaring the variable

Declare @orderBy char(1)
Set @orderBy = 'A'
SELECT START_DATE, CALL_ID
FROM CALL
ORDER BY CASE WHEN @orderBy = 'A' THEN START_DATE, CALL_ID END

So, I can't SET the variable. This will be a parameter passed in from my application and I won't know what the value will be beforehand (and even though I only included one case, there will be multiple cases in my actual query).

The DECLARE does get rid of the error message but does not give me the right results. I am also getting an error message when I try to include more than 1 value in the ORDER BY:

Msg 102, Level 15, State 1, Line 4
Incorrect syntax near ','.

[ck9663]

What kind of application are you running that calls this query? It might be possible you build your query dynamically on your app side and run it accordingly.

-- CK

[shlo]

What kind of application are you running that calls this query? It might be possible you build your query dynamically on your app side and run it accordingly.

-- CK

No, it's not possible to build the query dynamically. It's a web application that uses BIRT (reporting tool).

Since I've found it really hard to construct and test queries in BIRT, I've been constructing/testing them in SS first. My current work around is to have 3 different reports that only differ in their ORDER BY... this works, but i thought it would be possible (and cleaner, easier to maintain) to combine the reports and have a parameter that dynamically determines ORDER BY.

At this point however, I am not even able to get a simple query to work in BIRT or SS, ie. the code below give me errors:

SELECT * 
FROM CALL
ORDER BY @orderBy

It seems weird that the above code doesn't work even though I use parameters galore in the SELECT and WHERE clause in all the other reports - makes me wonder if it's something particular to the ORDER BY?? Maybe this is just not possible? If that's the case, I will just keep my work around which isn't pretty but it works :(

Thanks for your help.

[Delerna]

SELECT * 
FROM CALL
ORDER BY @orderBy
 

Order by works with field names. You can't use a variable in the order by like that

You can say

SELECT * 
FROM CALL
ORDER BY case when @orderBy='test' then FieldName1 else FieldName2 end
 

Also from a previous post

Declare @orderBy char(1)
Set @orderBy = 'A'
SELECT START_DATE, CALL_ID
FROM CALL
ORDER BY CASE WHEN @orderBy = 'A' THEN START_DATE, CALL_ID END
 

You can't reference multiple fields in the case like that
you need a case statement for each field

Declare @orderBy char(1)
Set @orderBy = 'A'
SELECT START_DATE, CALL_ID
FROM CALL
ORDER BY 
   CASE WHEN @orderBy = 'A' THEN START_DATE ELSE CALL_ID END,
   CASE WHEN @orderBy = 'A' THEN CALL_ID ELSE START_DATE END
 

So if @orderBy='A' it will be ORDER BY START_DATE,CALL _ID
otherwise it will be ORDER BY CALL_ID,START_D ATE



At least, not in SQLServer 2000 you can't