CODEWITHSUNDEEP
sqloracle-databaseplsqlcasewhere-clause
ajmalmhd04
ajmalmhd04

Reputation: 2602

Oracle case inside where clause

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

Answers (4)

sergdenisov
sergdenisov

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

Shij
Shij

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

John Bingham
John Bingham

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

Kirill Leontev
Kirill Leontev

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

Related Questions