CASE Statement in Where Clause?

[Largo SQL Tools]

Can anyone tell me if it's possible to use a Case statement in a Where
clause, and if so, the proper syntax?

J.R.
Largo SQL Tools
The Finest Collection of SQL Tools Available

http://www.largosqltools.com

[John Gilson]

Re: CASE Statement in Where Clause?

"Largo SQL Tools" <support@_REMOV E_largosqltools .com> wrote in message
news:v5adnTcJ84 Mdbh-iXTWJiw@buckeye-express.com...[color=blue]
> Can anyone tell me if it's possible to use a Case statement in a Where
> clause, and if so, the proper syntax?
>
> J.R.
> Largo SQL Tools
> The Finest Collection of SQL Tools Available
> http://www.largosqltools.com[/color]

CASE is an expression, not a statement, and, as such, returns a value.
It can indeed be used in a WHERE clause. For example,

SELECT *
FROM T
WHERE col1 = CASE WHEN col2 < col3
THEN col2
ELSE col3
END

Regards,
jag

[Shervin Shapourian]

Re: CASE Statement in Where Clause?

Sure you can. Unlike procedural languages, CASE in SQL is an expression. You
can use almost any kind of expressions in WHERE clause (aggregate functions
are exceptions).

This is an example of using CASE in WHERE clause:

select *
from YourTable
where SomeColumn = case
when Condition1 then Value1
when Condition2 then Value2
else Value3
end


Shervin

"Largo SQL Tools" <support@_REMOV E_largosqltools .com> wrote in message
news:v5adnTcJ84 Mdbh-iXTWJiw@buckeye-express.com...[color=blue]
> Can anyone tell me if it's possible to use a Case statement in a Where
> clause, and if so, the proper syntax?
>
> J.R.
> Largo SQL Tools
> The Finest Collection of SQL Tools Available
> http://www.largosqltools.com
>
>[/color]