10 Replies Latest reply: Dec 13, 2012 10:56 AM by hitgon

# If with multiple conditon "OR"

Hello ,
I am not sure what i am doing wrong in this should be some silly mistake but i am not getting around this. :(
I just used reverse condition to suit my situation but would like to know what casing this.
``````declare
rt varchar2(20);
begin

rt := 9;
if (rt != 9) OR (rt != 0) then
rt := 'Error -'||rt;
dbms_output.put_line(rt);
end if;
end;``````
output when rt := 9 then Error -9
output when rt := 11 then Error -11

Thanks
• ###### 1. Re: If with multiple conditon "OR"
The condition (rt!=0) is true, so rt will become 'Error -'||rt

hm
• ###### 2. Re: If with multiple conditon "OR"
There are some SQL functions that you could use here like DECODE or NVL
• ###### 3. Re: If with multiple conditon "OR"
Basic Boolean principle:

If any one (or more) conditions in an OR is true, then the result is true.

In your case, with rt equal to 9:
+(rt != 9) OR (rt != 0)+

Is:
+(FALSE) OR (TRUE)+

Result:
TRUE

The condition you have however does not make sense - and AND would result in a more logical and send sensible condition.
• ###### 4. Re: If with multiple conditon "OR"
hitgon wrote:
There are some SQL functions that you could use here like DECODE or NVL
Such functions do not automagically fix basic logic errors.

Get the logic right first. And then select the functions to apply that logic programatically.
• ###### 5. Re: If with multiple conditon "OR"
Here's why your condition test is not sensible - as one of the predicates will always be true... and thus make the condition check always true.
``````SQL> with test_data( r ) as(
2          select level-1 from dual
3          connect by level <= 10
4  )
5  select
6          r,
7          case when
8                  (r != 9 ) then 'TRUE'
9          else
10                  'FALSE'
11          end                     as "(r != 9)",
12          case when
13                  (r != 0) then 'TRUE'
14          else
15                  'FALSE'
16          end                     as "(r != 0)",
17          case when
18                  (r != 9 ) or (r != 0) then 'TRUE'
19          else
20                  'FALSE'
21          end                     as "Result"
22  from       test_data
23  order by r;

R (r != 9)   (r != 0)   Result
---------- ---------- ---------- ----------
0 TRUE       FALSE      TRUE
1 TRUE       TRUE       TRUE
2 TRUE       TRUE       TRUE
3 TRUE       TRUE       TRUE
4 TRUE       TRUE       TRUE
5 TRUE       TRUE       TRUE
6 TRUE       TRUE       TRUE
7 TRUE       TRUE       TRUE
8 TRUE       TRUE       TRUE
9 FALSE      TRUE       TRUE

10 rows selected.

SQL> ``````
• ###### 7. Re: If with multiple conditon "OR"
What is it you want.
Perhaps you mean?
``if rt NOT IN (9,0)``
Or similarly:
``if NOT ((rt = 9) or (rt=0)) then``
Also consider NULLs.
• ###### 8. Re: If with multiple conditon "OR"
Thanks Guys for all your suggestion/help i think i got an what was wrong here :) thanks!!!
• ###### 9. Re: If with multiple conditon "OR"
Ramesh Penti wrote:
``````IF true IS true THEN
``DO x``