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 Statement

Hello,
I am trying to understand how to use CASE statements within a CASE
statement. I am using the following but getting the error:
Incorrect syntax near the keyword 'CASE'.
declare @.MyDate DATETIME, @.YearType varchar(255)
set @.MyDate = getdate()
set @.YearType = 'fiscal'
Select
CASE @.YearType
WHEN 'fiscal' THEN
CASE WHEN month(@.MyDate) BETWEEN 1 AND 3 THEN 'Q02-' +
Convert(varchar,year(@.MyDate) - 1)
CASE WHEN month(@.MyDate) BETWEEN 4 AND 6 THEN 'Q03-' +
Convert(varchar,year(@.MyDate) - 1)
CASE WHEN month(@.MyDate) BETWEEN 7 AND 9 THEN 'Q04-' +
Convert(varchar,year(@.MyDate) - 1)
CASE WHEN month(@.MyDate) BETWEEN 10 AND 12 THEN 'Q04' +
Convert(varchar,year(@.MyDate))
WHEN 'calendar' THEN
CASE WHEN month(@.MyDate) BETWEEN 1 AND 3 THEN 'Q01-' +
Convert(varchar,year(@.MyDate) )
CASE WHEN month(@.MyDate) BETWEEN 4 AND 6 THEN 'Q02-' +
Convert(varchar,year(@.MyDate) )
CASE WHEN month(@.MyDate) BETWEEN 7 AND 9 THEN 'Q03-' +
Convert(varchar,year(@.MyDate) )
CASE WHEN month(@.MyDate) BETWEEN 10 AND 12 THEN 'Q04-' +
Convert(varchar,year(@.MyDate))
END
Any help would be appreciated.
--
Thanks in advance,
sck10You are missing END in the inner CASEs.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"sck10" <sck10@.online.nospam> wrote in message news:upANGW6XFHA.3620@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I am trying to understand how to use CASE statements within a CASE
> statement. I am using the following but getting the error:
> Incorrect syntax near the keyword 'CASE'.
> declare @.MyDate DATETIME, @.YearType varchar(255)
> set @.MyDate = getdate()
> set @.YearType = 'fiscal'
> Select
> CASE @.YearType
> WHEN 'fiscal' THEN
> CASE WHEN month(@.MyDate) BETWEEN 1 AND 3 THEN 'Q02-' +
> Convert(varchar,year(@.MyDate) - 1)
> CASE WHEN month(@.MyDate) BETWEEN 4 AND 6 THEN 'Q03-' +
> Convert(varchar,year(@.MyDate) - 1)
> CASE WHEN month(@.MyDate) BETWEEN 7 AND 9 THEN 'Q04-' +
> Convert(varchar,year(@.MyDate) - 1)
> CASE WHEN month(@.MyDate) BETWEEN 10 AND 12 THEN 'Q04' +
> Convert(varchar,year(@.MyDate))
> WHEN 'calendar' THEN
> CASE WHEN month(@.MyDate) BETWEEN 1 AND 3 THEN 'Q01-' +
> Convert(varchar,year(@.MyDate) )
> CASE WHEN month(@.MyDate) BETWEEN 4 AND 6 THEN 'Q02-' +
> Convert(varchar,year(@.MyDate) )
> CASE WHEN month(@.MyDate) BETWEEN 7 AND 9 THEN 'Q03-' +
> Convert(varchar,year(@.MyDate) )
> CASE WHEN month(@.MyDate) BETWEEN 10 AND 12 THEN 'Q04-' +
> Convert(varchar,year(@.MyDate))
> END
>
> Any help would be appreciated.
> --
> Thanks in advance,
> sck10
>|||On Mon, 23 May 2005 09:41:35 -0500, sck10 wrote:
>Hello,
>I am trying to understand how to use CASE statements within a CASE
>statement. I am using the following but getting the error:
>Incorrect syntax near the keyword 'CASE'.
Hi sck10,
Tibor already found the missing END's, but he seems to have missed the
extraneous CASE's.
Select
CASE @.YearType
WHEN 'fiscal' THEN
CASE WHEN month(@.MyDate) BETWEEN 1 AND 3 THEN 'Q02-' +
Convert(varchar,year(@.MyDate) - 1)
WHEN month(@.MyDate) BETWEEN 4 AND 6 THEN 'Q03-' +
Convert(varchar,year(@.MyDate) - 1)
WHEN month(@.MyDate) BETWEEN 7 AND 9 THEN 'Q04-' +
Convert(varchar,year(@.MyDate) - 1)
ELSE 'Q04' + Convert(varchar,year(@.MyDate))
END
WHEN 'calendar' THEN
CASE WHEN month(@.MyDate) BETWEEN 1 AND 3 THEN 'Q01-' +
Convert(varchar,year(@.MyDate) )
WHEN month(@.MyDate) BETWEEN 4 AND 6 THEN 'Q02-' +
Convert(varchar,year(@.MyDate) )
WHEN month(@.MyDate) BETWEEN 7 AND 9 THEN 'Q03-' +
Convert(varchar,year(@.MyDate) )
ELSE 'Q04-' + Convert(varchar,year(@.MyDate))
END
END
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)