CASE in a WHERE clause

Is it possible to use CASE in a WHERE clause? If yes hpw would the syntax look like.

Thanks

Roman

Yes you can use "Case" statement in Where clause.

Syntax could look something like :

select * from test_table
where (case when column_a = 'A' then 'B' else 'C') = ;


HTH.

I get an error when I try something like this? Any ideas?

WHERE
(CASE
WHEN d.carrier in ('AWS Test', 'AWS Lab') THEN 'AWS'
WHEN d.carrier = 'Bell Mobility - DataTac' THEN 'Bell Mobility - DataTac'
WHEN d.carrier IS NULL OR d.carrier in ('Unknown', 'Unknown Carrier') THEN 'Unknown Carrier'
ELSE d.carrier
END)
and c.dte between to_date ('01-Dec-2003') and to_date('01-Dec-2003')
and c.pin=d.pin(+)

The case statement is not a predicate in itself -- you need to compare it to something , like ...

Code:

---

WHERE
(CASE
WHEN d.carrier in ('AWS Test', 'AWS Lab') THEN 'AWS'
WHEN d.carrier = 'Bell Mobility - DataTac' THEN 'Bell Mobility - DataTac'
WHEN d.carrier IS NULL OR d.carrier in ('Unknown', 'Unknown Carrier') THEN 'Unknown Carrier'
ELSE d.carrier
END) = 'whatever'

---

This may not be the most efficient method though -- you'll not get any index-based access to d.carrier

Quote:

---

Originally posted by patel_dil
select * from test_table
where (case when column_a = 'A' then 'B' else 'C') = ;


HTH.

---

Above syntax was infact,

select * from test_table
where (case when column_a = 'A' then 'B' else 'C') = 'some value' ;

html did not like "<" and ">". ;)

Like slimdave said, the case statement will just return some value which you can use in predicate.

Thanks

Quote:

---

Originally posted by slimdave
This may not be the most efficient method though

---

TRUE, but my concern would be that the CASE structure adds a data transformation that might make the logic less easy to follow and maintain; it can be easly rewritten without the CASE (perhaps with better performance). Unless the case structure is a copy/paste job from elsewhere, purely to enhance maintainability . . . .