Case statement Error

Hi,
I don't know what i am doing wrong here, I am always getting this error
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near '>'.
When I am running the following query
SELECT title, price,
Budget = CASE price
WHEN price > 20.00 THEN 'Expensive'
WHEN price BETWEEN 10.00 AND 19.99 THEN 'Moderate'
WHEN price < 10.00 THEN 'Inexpensive'
ELSE 'Unknown'
END
FROM titles
Could anyone put some light on it.
Thanks
J S"John Smith" <John@.nospam.yahoo.com> wrote in news:ebqEpwfOFHA.1500
@.TK2MSFTNGP09.phx.gbl:

> Hi,
> I don't know what i am doing wrong here, I am always getting this error
> Server: Msg 170, Level 15, State 1, Line 3
> Line 3: Incorrect syntax near '>'.
> When I am running the following query
> SELECT title, price,
> Budget = CASE price
> WHEN price > 20.00 THEN 'Expensive'
> WHEN price BETWEEN 10.00 AND 19.99 THEN 'Moderate'
> WHEN price < 10.00 THEN 'Inexpensive'
> ELSE 'Unknown'
> END
> FROM titles
> Could anyone put some light on it.
> Thanks
> J S
>
>
Leave out the field name between the CASE and WHEN keywords.
Rumble
"Write something worth reading, or do something worth writing."
-- Benjamin Franklin|||There are two versions of Case Syntax, and you are "mixing" them...
Ver 1
Case <Expression>
When <ExpressionValue1> Then <OutValue1>
When <ExpressionValue2> Then <OutValue2>
When <ExpressionValue3> Then <OutValue3>
Else <ElseOutValue> End
Version 2
Case
When <BooleanExpression1> Then <OutValue1>
When <BooleanExpression2> Then <OutValue2>
When <BooleanExpression3> Then <OutValue3>
Else <ElseOutValue> End
So you need to eliminate the "price" right after the Case...
SELECT title, price,
Budget = CASE
WHEN price > 20.00 THEN 'Expensive'
WHEN price BETWEEN 10.00 AND 19.99 THEN 'Moderate'
WHEN price < 10.00 THEN 'Inexpensive'
ELSE 'Unknown'
END
FROM titles
"John Smith" wrote:

> Hi,
> I don't know what i am doing wrong here, I am always getting this error
> Server: Msg 170, Level 15, State 1, Line 3
> Line 3: Incorrect syntax near '>'.
> When I am running the following query
> SELECT title, price,
> Budget = CASE price
> WHEN price > 20.00 THEN 'Expensive'
> WHEN price BETWEEN 10.00 AND 19.99 THEN 'Moderate'
> WHEN price < 10.00 THEN 'Inexpensive'
> ELSE 'Unknown'
> END
> FROM titles
> Could anyone put some light on it.
> Thanks
> J S
>
>|||this should work:
SELECT title, price,
Budget = CASE --price <- removed
WHEN price > 20.00 THEN 'Expensive'
WHEN price BETWEEN 10.00 AND 19.99 THEN 'Moderate'
WHEN price < 10.00 THEN 'Inexpensive'
ELSE 'Unknown'
END
FROM titles
dean
"John Smith" <John@.nospam.yahoo.com> wrote in message
news:ebqEpwfOFHA.1500@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I don't know what i am doing wrong here, I am always getting this error
> Server: Msg 170, Level 15, State 1, Line 3
> Line 3: Incorrect syntax near '>'.
> When I am running the following query
> SELECT title, price,
> Budget = CASE price
> WHEN price > 20.00 THEN 'Expensive'
> WHEN price BETWEEN 10.00 AND 19.99 THEN 'Moderate'
> WHEN price < 10.00 THEN 'Inexpensive'
> ELSE 'Unknown'
> END
> FROM titles
> Could anyone put some light on it.
> Thanks
> J S
>|||Thanks a lot guys, it is working....
J S
"John Smith" <John@.nospam.yahoo.com> wrote in message
news:ebqEpwfOFHA.1500@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I don't know what i am doing wrong here, I am always getting this error
> Server: Msg 170, Level 15, State 1, Line 3
> Line 3: Incorrect syntax near '>'.
> When I am running the following query
> SELECT title, price,
> Budget = CASE price
> WHEN price > 20.00 THEN 'Expensive'
> WHEN price BETWEEN 10.00 AND 19.99 THEN 'Moderate'
> WHEN price < 10.00 THEN 'Inexpensive'
> ELSE 'Unknown'
> END
> FROM titles
> Could anyone put some light on it.
> Thanks
> J S
>|||I removed price from CASE. This passed syntax check for me:
SELECT title, price,
Budget = CASE
WHEN price > 20.00 THEN 'Expensive'
WHEN price BETWEEN 10.00 AND 19.99 THEN 'Moderate'
WHEN price < 10.00 THEN 'Inexpensive'
ELSE 'Unknown'
END
FROM titles

CASE Issue

When I set @.reorderfav = 1 or @.reorderfav = 3, it works perfect. However,
when i set @.reorderfav = 2, it throws this error:
Server: Msg 241, Level 16, State 1, Line 7
Syntax error converting datetime from character string.
I have also put the ProjectMain.ProjectName in the ORDER BY and ran it
manually, and it works. This is why I'm so . Any help would be
greatly appreciated.
Thanks,
JJ
CREATE PROCEDURE [usp_getindexFav]
(
@.userid varchar(6),
@.reorderfav int
)
AS
SELECT AppUserPref.UserID, ProjectMain.ProjectName,
ProjectMain.ProjectID, ProjectMain.CreatedPostDate
FROM AppUserPref INNER JOIN ProjectMain ON AppUserPref.ProjectID =
ProjectMain.ProjectID
WHERE AppUserPref.ID = @.userid
ORDER BY CASE
WHEN @.reorderfav = 1 THEN ProjectMain.ProjectID
WHEN @.reorderfav = 2 THEN ProjectMain.ProjectName
WHEN @.reorderfav = 3 THEN ProjectMain.CreatedPostDate
ELSE ProjectMain.ProjectName
END
GO
Manually (this works):
SELECT AppUserPref.UserID, ProjectMain.ProjectName,
ProjectMain.ProjectID, ProjectMain.CreatedPostDate
FROM AppUserPref INNER JOIN ProjectMain ON AppUserPref.ProjectID =
ProjectMain.ProjectID
WHERE AppUserPref.ID = @.userid
ORDER BY ProjectMain.ProjectNameCASE is an expression that can only return a value of the same data type.
You will need to break it out so that each potential data type is handled by
a separate CASE expression.
http://www.aspfaq.com/2501
http://www.aspfaq.com/
(Reverse address to reply.)
"Josh Jones" <myaddress@.thidress.com> wrote in message
news:uhvG0RrGFHA.3724@.TK2MSFTNGP10.phx.gbl...
> When I set @.reorderfav = 1 or @.reorderfav = 3, it works perfect. However,
> when i set @.reorderfav = 2, it throws this error:
> Server: Msg 241, Level 16, State 1, Line 7
> Syntax error converting datetime from character string.
> I have also put the ProjectMain.ProjectName in the ORDER BY and ran it
> manually, and it works. This is why I'm so . Any help would be
> greatly appreciated.
> Thanks,
> JJ
> CREATE PROCEDURE [usp_getindexFav]
> (
> @.userid varchar(6),
> @.reorderfav int
> )
> AS
>
> SELECT AppUserPref.UserID, ProjectMain.ProjectName,
> ProjectMain.ProjectID, ProjectMain.CreatedPostDate
> FROM AppUserPref INNER JOIN ProjectMain ON AppUserPref.ProjectID =
> ProjectMain.ProjectID
> WHERE AppUserPref.ID = @.userid
> ORDER BY CASE
> WHEN @.reorderfav = 1 THEN ProjectMain.ProjectID
> WHEN @.reorderfav = 2 THEN ProjectMain.ProjectName
> WHEN @.reorderfav = 3 THEN ProjectMain.CreatedPostDate
> ELSE ProjectMain.ProjectName
> END
> GO
>
> Manually (this works):
> SELECT AppUserPref.UserID, ProjectMain.ProjectName,
> ProjectMain.ProjectID, ProjectMain.CreatedPostDate
> FROM AppUserPref INNER JOIN ProjectMain ON AppUserPref.ProjectID =
> ProjectMain.ProjectID
> WHERE AppUserPref.ID = @.userid
> ORDER BY ProjectMain.ProjectName
>|||"Josh Jones" <myaddress@.thidress.com> wrote in message
news:uhvG0RrGFHA.3724@.TK2MSFTNGP10.phx.gbl...

> CREATE PROCEDURE [usp_getindexFav]
> (
> @.userid varchar(6),
> @.reorderfav int
> )
> AS
>
> SELECT AppUserPref.UserID, ProjectMain.ProjectName,
> ProjectMain.ProjectID, ProjectMain.CreatedPostDate
> FROM AppUserPref INNER JOIN ProjectMain ON AppUserPref.ProjectID =
> ProjectMain.ProjectID
> WHERE AppUserPref.ID = @.userid
> ORDER BY CASE
> WHEN @.reorderfav = 1 THEN ProjectMain.ProjectID
> WHEN @.reorderfav = 2 THEN ProjectMain.ProjectName
> WHEN @.reorderfav = 3 THEN ProjectMain.CreatedPostDate
> ELSE ProjectMain.ProjectName
> END
Case requires all output values to be of the same type and tries to convert
if not. Since projectName can't be converted to a date it breaks. Try this
for the order by
ORDER BY
CASE WHEN @.reorderfav = 1 then ProjectMain.ProjectID END,
CASE WHEN @.reorderfav = 2 then ProjectMain.ProjectName END,
CASE WHEN @.reorderfav = 3 then ProjectMain.CreatedPostDate END,
ProjectMain.ProjectName
Good Luck,
Jim|||Jim--good quick answer!!
Thanks!!!
"Josh Jones" <myaddress@.thidress.com> wrote in message
news:uhvG0RrGFHA.3724@.TK2MSFTNGP10.phx.gbl...
> When I set @.reorderfav = 1 or @.reorderfav = 3, it works perfect. However,
> when i set @.reorderfav = 2, it throws this error:
> Server: Msg 241, Level 16, State 1, Line 7
> Syntax error converting datetime from character string.
> I have also put the ProjectMain.ProjectName in the ORDER BY and ran it
> manually, and it works. This is why I'm so . Any help would be
> greatly appreciated.
> Thanks,
> JJ
> CREATE PROCEDURE [usp_getindexFav]
> (
> @.userid varchar(6),
> @.reorderfav int
> )
> AS
>
> SELECT AppUserPref.UserID, ProjectMain.ProjectName,
> ProjectMain.ProjectID, ProjectMain.CreatedPostDate
> FROM AppUserPref INNER JOIN ProjectMain ON AppUserPref.ProjectID =
> ProjectMain.ProjectID
> WHERE AppUserPref.ID = @.userid
> ORDER BY CASE
> WHEN @.reorderfav = 1 THEN ProjectMain.ProjectID
> WHEN @.reorderfav = 2 THEN ProjectMain.ProjectName
> WHEN @.reorderfav = 3 THEN ProjectMain.CreatedPostDate
> ELSE ProjectMain.ProjectName
> END
> GO
>
> Manually (this works):
> SELECT AppUserPref.UserID, ProjectMain.ProjectName,
> ProjectMain.ProjectID, ProjectMain.CreatedPostDate
> FROM AppUserPref INNER JOIN ProjectMain ON AppUserPref.ProjectID =
> ProjectMain.ProjectID
> WHERE AppUserPref.ID = @.userid
> ORDER BY ProjectMain.ProjectName
>

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