This is a simple question, I’ve read some details about using CASE
in WHERE
clause, but couldn’t able to make a clear idea how to use it. The below is my sample query:
x
1 SELECT * FROM dual
2 WHERE (1 =1)
3 AND (SYSDATE+1 > SYSDATE)
4 AND (30 > 40)
5 AND (25 < 35);
I have a procedure i_value
as in parameter.
I need to ignore the 4th line if i_value is ‘S’ and I need to ignore the 5th line if i_value is ‘T’.
Thanks in advance.
Advertisement
Answer
I think this is the best way to solve your problem:
select *
from dual
where (1 = 1)
and (sysdate + 1 > sysdate)
and case
when i_value = 'S'
then
case
when (25 < 35)
then 1
else 0
end
when i_value = 'T'
then
case
when (30 > 40)
then 1
else 0
end
end = 1;
Of course, you could use Dynamic SQL, but it’d be more difficult and less effective.