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.