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 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!

What error are you getting?

You can actually just do this:

SELECT     COUNT(*) 
FROM         CALL
WHERE     CALL.DESCRIPTION =  @Param1

-- CK

[shlo]

What error are you getting?

You can actually just do this:

SELECT     COUNT(*) 
FROM         CALL
WHERE     CALL.DESCRIPTION =  @Param1

-- CK

Sorry, I forgot my else statement in my sample code:

What I'm trying to do is set get the count of calls based on the parameter. The parameter determines whether call description is equal to 'In', 'Out', or 'Any'. If any, I want to view calls with description of 'In' or 'Out'.

So,
if @Param1 = 'In', then call.descriptio n = 'In'
if @Param1 = 'Out', then call.descriptio n = 'Out'
if @Param1 = 'Any' then call.descriptio n = 'In' OR call.descriptio n = 'Out'

This is why I was using a case statement. Any suggestions would be greatly appreciated!! And I was getting an error that 'Parameter is Incorrect'.

[ck9663]

So if I understand it right:

if @param = 'In' then you want all rows with CALL.DESCRIPTIO N = In'.
if @param = 'Out' then you want all rows with CALL.DESCRIPTIO N = Out'.
if @param = 'Any' then you want all rows, both CALL.DESCRIPTIO N = 'In' or CALL.DESCRIPTIO N = 'Out'

If my assumption is wrong, don't read the following, otherwise try the following:

SELECT COUNT(*)
FROM CALL
WHERE (CALL.DESCRIPTION =
CASE
WHEN @Param1 = 'In' THEN 'In'
WHEN @Param1 = 'Out' THEN 'Out'
END) or  (@Param1 = 'Any' and CALL.DESCRIPTION in ('In','Out'))

-- CK

[shlo]

So if I understand it right:

if @param = 'In' then you want all rows with CALL.DESCRIPTIO N = In'.
if @param = 'Out' then you want all rows with CALL.DESCRIPTIO N = Out'.
if @param = 'Any' then you want all rows, both CALL.DESCRIPTIO N = 'In' or CALL.DESCRIPTIO N = 'Out'

If my assumption is wrong, don't read the following, otherwise try the following:

SELECT COUNT(*)
FROM CALL
WHERE (CALL.DESCRIPTION =
CASE
WHEN @Param1 = 'In' THEN 'In'
WHEN @Param1 = 'Out' THEN 'Out'
END) or  (@Param1 = 'Any' and CALL.DESCRIPTION in ('In','Out'))

-- CK

That worked perfectly. Thanks so much for your help!

[shlo]

Another question about case statements, now in ORDER BY.
Here is my query:

SELECT START_DATE, CALL_ID
FROM CALL

ORDER BY
CASE WHEN @OrderBy ='A' THEN START_DATE, CALL_ID
WHEN @OrderBy ='D' THEN START_DATE DESC, CALL_ID
WHEN @OrderBy ='R' THEN CALL_ID, START_DATE DESC
END

I looked up a bunch of articles and they seem to give this syntax. But, I get a SQL Execution Error with a message: Must declare the scalar variable @OrderBy. I am not doing this in a context of a stored procedure, so what am I missing?

Thanks in advance...

[ck9663]

Anything that starts with "@" is a variable. If you mean column, remove that symbol.

-- CK

[shlo]

Anything that starts with "@" is a variable. If you mean column, remove that symbol.

-- CK

I meant it to be a parameter that dynamically determines the ORDER BY. Since this is not a stored procedure, however, I don't know how to define the parameter before run time. Does this make sense?

[ck9663]

Where are you running it? SQL Analyzer?

The query needs to know the value of those variable before it runs. You have to declare it and store a value to it before your query can run.

-- CK

[shlo]

Where are you running it? SQL Analyzer?

The query needs to know the value of those variable before it runs. You have to declare it and store a value to it before your query can run.

-- CK

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.

[code=sql]SELECT *
FROM CALL
WHERE CALL.DESCRIPTIO N = @Param1[/code]

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

[code=sql]SELECT START_DATE, CALL_ID
FROM CALL
ORDER BY CASE WHEN @orderBy = 'A' THEN START_DATE, CALL_ID END[/code]

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.

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

[code=sql]SELECT *
FROM CALL
WHERE CALL.DESCRIPTIO N = @Param1[/code]

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

[code=sql]SELECT START_DATE, CALL_ID
FROM CALL
ORDER BY CASE WHEN @orderBy = 'A' THEN START_DATE, CALL_ID END[/code]

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
[code=sql]
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[/code]

[shlo]

Try declaring the variable
[code=sql]
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[/code]

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:

[code=sql]SELECT *
FROM CALL
ORDER BY @orderBy[/code]

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.