Oracle case in where is the sentence

Question

This is a simple question, I read some information about using CASE in WHERE , but could not give a clear idea of ​​how to use it. The following is an example of my query:

 1 SELECT * FROM dual 2 WHERE (1 =1) 3 AND (SYSDATE+1 > SYSDATE) 4 AND (30 > 40) 5 AND (25 < 35); 

I have an i_value procedure, as in a 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.

Answer 1

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 can use dynamic SQL, but it will be more complex and less efficient.

Answer 2

  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; 

Answer 3

Why is so ser using 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' ); 

Answer 4

This should also work.

 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