Case Expression in SQL Server 2000

I have to use the Case expression in my query, so I search arround the web and got the following:

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

It should run OK base on my research in the internet. But my SQL Server gave me error:

syntax error arround '>'.

I did several search and many people can use the ">" sign or "<" sign in the Case expression, but I just can't use it in my SQL Server, I can't even use any boolean expression, I can only use values.

can anyone help me out? My SQL Server Version is SQL Server 2000 Sevice Pack 4.

Thanks!

Try this:

SELECT title, price, CASE price AS Budget
WHEN > 20.00 THEN 'Expensive'
WHEN BETWEEN 10.00 AND 19.99 THEN 'Moderate'
WHEN < 10.00 THEN 'Inexpensive'
ELSE 'Unknown'
END,
FROM titles

|||

come out error too.

Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '>'.

|||

SELECT title, price,

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 AS Budget
FROM titles

|||

The case expression has 2 formats:

Simple CASE function:

CASEinput_expression
WHENwhen_expressionTHENresult_expression
[ ...n]
[
ELSEelse_result_expression
]
END

Searched CASE function:

CASE
WHENBoolean_expression THENresult_expression
[ ...n]
[
ELSEelse_result_expression
]
END

So you're using the simple CASE function when you add 'price' column following CASE keyword. Note this will evaluateinput_expression, and then, in the order specified, evaluatesinput_expression=when_expression for each WHEN clause. That's why CASE price WHEN price>N doesn't work. For more information about CASE, you can take a look at:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_5t9v.asp

|||

Great, Thanks so much for you answer! funny thing is I searched so many web sites, they all have bad Case expression..