-
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.
-- Dilip
-
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
-
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
-- Dilip
-
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 . . . .
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|