This discussion is archived
10 Replies Latest reply: Dec 13, 2012 8:56 AM by hitgon

If with multiple conditon "OR"

Currently Being Moderated
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"
Currently Being Moderated
The condition (rt!=0) is true, so rt will become 'Error -'||rt

hm
• 2. Re: If with multiple conditon "OR"
Currently Being Moderated
There are some SQL functions that you could use here like DECODE or NVL
• 3. Re: If with multiple conditon "OR"
Currently Being Moderated
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"
Currently Being Moderated
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"
Currently Being Moderated
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> ``````
• 6. Re: If with multiple conditon "OR"
Currently Being Moderated
• 7. Re: If with multiple conditon "OR"
Currently Being Moderated
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"
Currently Being Moderated
Thanks Guys for all your suggestion/help i think i got an what was wrong here :) thanks!!!
• 9. Re: If with multiple conditon "OR"
Currently Being Moderated
Ramesh Penti wrote:
No it is not.

Explain what is correct to code an IF condition that is always true?

This is extra code and just silly:
``````IF true IS true THEN
DO x
END IF``````
As the following does the EXACT same thing with a SINGLE statement:
``DO x``
Code is never correct simply because the syntax is correct and it compiles without an error.
• 10. Re: If with multiple conditon "OR"
Currently Being Moderated
Thanks Billy Verreynne for great explanation

Legend

• Correct Answers - 10 points