problems with case statement in where clause | Newbie | | Join Date: Aug 2008
Posts: 8
| | |
I was wondering if someone would be able to tell me what is wrong with my code here:
SELECT COUNT(*)
FROM CALL
WHERE CALL.DESCRIPTION =
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.Description to either 'In' or 'Out'.
Thanks in advance!
|  | Expert | | Join Date: Jun 2007
Posts: 1,925
| | | re: problems with case statement in where clause
Quote:
Originally Posted by 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.DESCRIPTION =
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.Description 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
| | Newbie | | Join Date: Aug 2008
Posts: 8
| | | re: problems with case statement in where clause
Quote:
Originally Posted by ck9663 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.description = 'In'
if @Param1 = 'Out', then call.description = 'Out'
if @Param1 = 'Any' then call.description = 'In' OR call.description = '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'.
| | Expert | | Join Date: Jun 2007
Posts: 1,925
| | | re: problems with case statement in where clause
So if I understand it right:
if @param = 'In' then you want all rows with CALL.DESCRIPTION = In'.
if @param = 'Out' then you want all rows with CALL.DESCRIPTION = Out'.
if @param = 'Any' then you want all rows, both CALL.DESCRIPTION = 'In' or CALL.DESCRIPTION = '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
| | Newbie | | Join Date: Aug 2008
Posts: 8
| | | re: problems with case statement in where clause
Quote:
Originally Posted by ck9663 So if I understand it right:
if @param = 'In' then you want all rows with CALL.DESCRIPTION = In'.
if @param = 'Out' then you want all rows with CALL.DESCRIPTION = Out'.
if @param = 'Any' then you want all rows, both CALL.DESCRIPTION = 'In' or CALL.DESCRIPTION = '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!
| | Newbie | | Join Date: Aug 2008
Posts: 8
| | | re: problems with case statement in where clause
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...
| | Expert | | Join Date: Jun 2007
Posts: 1,925
| | | re: problems with case statement in where clause
Anything that starts with "@" is a variable. If you mean column, remove that symbol.
-- CK
| | Newbie | | Join Date: Aug 2008
Posts: 8
| | | re: problems with case statement in where clause
Quote:
Originally Posted by ck9663 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?
| | Expert | | Join Date: Jun 2007
Posts: 1,925
| | | re: problems with case statement in where clause
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
| | Newbie | | Join Date: Aug 2008
Posts: 8
| | | re: problems with case statement in where clause
Quote:
Originally Posted by 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 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.
| | Expert | | Join Date: Jun 2007
Posts: 1,925
| | | re: problems with case statement in where clause
I believe the columns on the ORDER BY clause must be on your dataset or use ordinal position.
-- CK
| | Newbie | | Join Date: Aug 2008
Posts: 3
| | | re: problems with case statement in where clause
Quote:
Originally Posted by shlo 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
| | Newbie | | Join Date: Aug 2008
Posts: 8
| | | re: problems with case statement in where clause
Quote:
Originally Posted by ShadowTech 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 ','.
| | Expert | | Join Date: Jun 2007
Posts: 1,925
| | | re: problems with case statement in where clause
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
| | Newbie | | Join Date: Aug 2008
Posts: 8
| | | re: problems with case statement in where clause
Quote:
Originally Posted by 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 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.
|  | Expert | | Join Date: Jan 2008 Location: Sydney
Posts: 790
| | | re: problems with case statement in where clause
-
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_DATE
At least, not in SQLServer 2000 you can't
|  |
Similar Microsoft SQL Server bytes | | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,537 network members.
|
