Case in Where Clause

Can you put a Case statement in a Where clause?
I have the following Cursor I am setting up that is giving me the error:
Server: Msg 170, Level 15, State 1, Line 26
Line 26: Incorrect syntax near '>'.
Declare @.SearchCursor Cursor
Set @.SearchCursor = Cursor for Select
CommandText,WhereClause,SearchID,UserID
from CandidateSearches where SearchAgent=1 and SearchAgentActive=1 and
(CASE WHEN NotifyFrequency = 'D' THEN
(DATEDIFF(DAY,SearchAgentLastRun,GetDate
()) > 0)
WHEN NotifyFrequency = 'W' THEN
(DATEDIFF(DAY,SearchAgentLastRun,GetDate
()) >= 7)
WHEN NotifyFrequency = 'M' THEN
(DATEDIFF(DAY,SEarchAgentLastRun,GetDate
()) >= 30) END)
Line 26 is the line the Case Statement is on.
What is the problem with this line?
Thanks,
TomHi Tom
The most important thing to keep in mind is that there is no case STATEMENT
in Transact-SQL. There is a case EXPRESSION, which can be used anywhere you
use an expression. So, you can use a case EXPRESSION in a WHERE, in place of
a value.
Without any more details like the DDL, or a description of what you're
trying to accomplish, my guess is that you want to compare
DATEDIFF(DAY,SearchAgentLastRun,GetDate(
)) to one of several different
values, depending on the value of NotifyFrequency. If that is a correct
understanding, you might try something like this in your WHERE clause:
where SearchAgent=1 and SearchAgentActive=1 and
DATEDIFF(DAY,SearchAgentLastRun,GetDate(
)) >= CASE NotifyFrequency
WHEN 'D' THEN 0
WHEN 'W' THEN 7
WHEN 'M' THEN 30
END
Also make sure to consider the case where NotifyFrequency is not one of (D,
W, M)
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:eeyXs5cMGHA.3960@.TK2MSFTNGP09.phx.gbl...
> Can you put a Case statement in a Where clause?
> I have the following Cursor I am setting up that is giving me the error:
> Server: Msg 170, Level 15, State 1, Line 26
> Line 26: Incorrect syntax near '>'.
> Declare @.SearchCursor Cursor
> Set @.SearchCursor = Cursor for Select
> CommandText,WhereClause,SearchID,UserID
> from CandidateSearches where SearchAgent=1 and SearchAgentActive=1 and
> (CASE WHEN NotifyFrequency = 'D' THEN
> (DATEDIFF(DAY,SearchAgentLastRun,GetDate
()) > 0)
> WHEN NotifyFrequency = 'W' THEN
> (DATEDIFF(DAY,SearchAgentLastRun,GetDate
()) >= 7)
> WHEN NotifyFrequency = 'M' THEN
> (DATEDIFF(DAY,SEarchAgentLastRun,GetDate
()) >= 30) END)
> Line 26 is the line the Case Statement is on.
> What is the problem with this line?
> Thanks,
> Tom
>|||"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OQDXBGdMGHA.500@.TK2MSFTNGP15.phx.gbl...
> Hi Tom
> The most important thing to keep in mind is that there is no case
> STATEMENT in Transact-SQL. There is a case EXPRESSION, which can be used
> anywhere you use an expression. So, you can use a case EXPRESSION in a
> WHERE, in place of a value.
> Without any more details like the DDL, or a description of what you're
> trying to accomplish, my guess is that you want to compare
> DATEDIFF(DAY,SearchAgentLastRun,GetDate(
)) to one of several different
> values, depending on the value of NotifyFrequency. If that is a correct
> understanding, you might try something like this in your WHERE clause:
Exactly, but how would I do the instance where the case of 'D' is > 0 and
not >= 0?
Thanks,
Tom

> where SearchAgent=1 and SearchAgentActive=1 and
> DATEDIFF(DAY,SearchAgentLastRun,GetDate(
)) >= CASE NotifyFrequency
> WHEN 'D' THEN 0
> WHEN 'W' THEN 7
> WHEN 'M' THEN 30
> END
> Also make sure to consider the case where NotifyFrequency is not one of
> (D, W, M)
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:eeyXs5cMGHA.3960@.TK2MSFTNGP09.phx.gbl...
>
>|||I think what you are trying to do is this
Declare @.SearchCursor Cursor
Set @.SearchCursor = Cursor for Select
CommandText,WhereClause,SearchID,UserID
from CandidateSearches where SearchAgent=1 and
SearchAgentActive=1 and
((NotifyFrequency = 'D'
and DATEDIFF(DAY,SearchAgentLastRun,GetDate(
)) > 0) OR
(NotifyFrequency = 'W'
and DATEDIFF(DAY,SearchAgentLastRun,GetDate(
)) >= 7) OR
(NotifyFrequency = 'M'
and DATEDIFF(DAY,SearchAgentLastRun,GetDate(
)) >= 30))|||<markc600@.hotmail.com> wrote in message
news:1140022428.091421.209720@.g14g2000cwa.googlegroups.com...
>I think what you are trying to do is this
> Declare @.SearchCursor Cursor
> Set @.SearchCursor = Cursor for Select
> CommandText,WhereClause,SearchID,UserID
> from CandidateSearches where SearchAgent=1 and
> SearchAgentActive=1 and
> ((NotifyFrequency = 'D'
> and DATEDIFF(DAY,SearchAgentLastRun,GetDate(
)) > 0) OR
> (NotifyFrequency = 'W'
> and DATEDIFF(DAY,SearchAgentLastRun,GetDate(
)) >= 7) OR
> (NotifyFrequency = 'M'
> and DATEDIFF(DAY,SearchAgentLastRun,GetDate(
)) >= 30))
That was exactly what I ended up doing. I was just curious as to how to it
(if you can do it) using the Case statement.
Thanks,
Tom|||>> Declare @.SearchCursor Cursor
>That was exactly what I ended up doing. I was just curious as to how to it
>(if you can do it) using the Case statement.
You can do it using CASE, but it doesn't let you do anything that AND,
OR and () don't already let you do. Since it resolves to a values,
you have to use it to set a value that indicates success or failure,
then test that in a comparison.
SELECT CommandText, WhereClause, SearchID, UserID
FROM CandidateSearches
WHERE SearchAgent=1
AND SearchAgentActive=1
AND CASE
WHEN NotifyFrequency = 'D'
AND DATEDIFF(DAY,SearchAgentLastRun,GetDate(
)) > 0
THEN 1
WHEN NotifyFrequency = 'W'
AND DATEDIFF(DAY,SearchAgentLastRun,GetDate(
)) >= 7
THEN 1
WHEN NotifyFrequency = 'M'
AND DATEDIFF(DAY,SearchAgentLastRun,GetDate(
)) >= 30
THEN 1
ELSE 0
END = 1
Roy|||"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:31l7v1dnjf97n234q7ih23mnbq4rtti9u1@.
4ax.com...
> You can do it using CASE, but it doesn't let you do anything that AND,
> OR and () don't already let you do. Since it resolves to a values,
> you have to use it to set a value that indicates success or failure,
> then test that in a comparison.
> SELECT CommandText, WhereClause, SearchID, UserID
> FROM CandidateSearches
> WHERE SearchAgent=1
> AND SearchAgentActive=1
> AND CASE
> WHEN NotifyFrequency = 'D'
> AND DATEDIFF(DAY,SearchAgentLastRun,GetDate(
)) > 0
> THEN 1
> WHEN NotifyFrequency = 'W'
> AND DATEDIFF(DAY,SearchAgentLastRun,GetDate(
)) >= 7
> THEN 1
> WHEN NotifyFrequency = 'M'
> AND DATEDIFF(DAY,SearchAgentLastRun,GetDate(
)) >= 30
> THEN 1
> ELSE 0
> END = 1
>
I was as to what this did at first, but then I realized what it was
doing.
In the "OR" example, I would only get a record if one of the 3 tests (as
well as the SearchAgent and SearchAgentActive) were matched.
In your example, you are testing if the Case statement was "1". It was
clear when I put parens around the case statement. I thought at first you
were setting "End=1". But when I put the parans in, it made sense:
... AND (CASE
WHEN NotifyFrequency = 'D'
AND DATEDIFF(DAY,SearchAgentLastRun,GetDate(
)) > 0
THEN 1
WHEN NotifyFrequency = 'W'
AND DATEDIFF(DAY,SearchAgentLastRun,GetDate(
)) >= 7
THEN 1
WHEN NotifyFrequency = 'M'
AND DATEDIFF(DAY,SearchAgentLastRun,GetDate(
)) >= 30
THEN 1
ELSE 0
END) = 1
I think that is what is happening.
But as you said the OR does the same thing and is a little more clear and
you don't have to do the extra step of setting it to 0 or 1.
Thanks,
Tom
> Roy