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