Reputation: 2602
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:
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.
Upvotes: 7
Views: 27857
Reputation: 8572
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.
Upvotes: 4
Reputation: 11
This should work as well.
case
when (i_value = 'S' and (WHERE (1=1) AND (SYSDATE+1 > SYSDATE) AND (25 < 35)) ) then 1
when (i_value = 'T' and (WHERE (1=1) AND (SYSDATE+1 > SYSDATE) AND (30 < 40)) ) then 1
else 0
end = 1
Upvotes: 0
Reputation: 2006
SELECT * FROM dual
WHERE (1 =1)
AND (SYSDATE+1 > SYSDATE)
AND CASE WHEN i_value = 'S' THEN 1 ELSE CASE WHEN (30 > 40) THEN 1 ELSE 0 END END = 1
AND CASE WHEN i_value = 'T' THEN 1 ELSE CASE WHEN (25 < 35) THEN 1 ELSE 0 END END = 1;
Upvotes: 2
Reputation: 10941
Why so ser use case
?
SELECT * FROM dual
WHERE (1 =1)
AND ( SYSDATE+1 > SYSDATE )
AND ( ((30 > 40) and i_value <> 'S') or i_value = 'S' )
AND ( ((25 < 35) and i_value <> 'T') or i_value = 'T' );
Upvotes: 2