Skip to content
Advertisement

CASE expression in WHERE clause for diferent and

Can I use case expression to build where like this?

select * 
from table 
where
case 
    when x=y then z= j and t=v
    when x=k then q= p and s=l
end
;

I need change where clause depending on the value of x variable.

Advertisement

Answer

An alternative to using OR is to use nested CASE statements:

SELECT * 
FROM   table_name 
WHERE  CASE 
       WHEN x = y THEN CASE WHEN z = j AND t = v THEN 1 ELSE 0 END
       WHEN x = k THEN CASE WHEN q = p AND s = l THEN 1 ELSE 0 END
       ELSE 0
       END = 1;

or you could simplify it to:

SELECT * 
FROM   table_name 
WHERE  CASE 
       WHEN x = y AND z = j AND t = v THEN 1
       WHEN x = k AND q = p AND s = l THEN 1
       ELSE 0
       END = 1;

However, you should check whether Oracle can use column indexes or if a separate function-based index is required with these solutions.

User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement