CODEWITHSUNDEEP
sqloracle-database
exferos
exferos

Reputation: 13

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.

Upvotes: 0

Views: 76

Answers (2)

MT0
MT0

Reputation: 167982

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.

Upvotes: 0

GMB
GMB

Reputation: 222462

Use or:

select * 
from table 
where (x = y and z = j and t = v) or (x = k and q = p and s = l);

Upvotes: 2

Related Questions