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

    If with multiple conditon "OR"

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

          hm
          • 2. Re: If with multiple conditon "OR"
            hitgon
            There are some SQL functions that you could use here like DECODE or NVL
            • 3. Re: If with multiple conditon "OR"
              Billy~Verreynne
              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"
                Billy~Verreynne
                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"
                  Billy~Verreynne
                  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"
                    798686
                    Your code is correct...
                    • 7. Re: If with multiple conditon "OR"
                      Dom Brooks
                      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"
                        user10647455
                        Thanks Guys for all your suggestion/help i think i got an what was wrong here :) thanks!!!
                        • 9. Re: If with multiple conditon "OR"
                          Billy~Verreynne
                          Ramesh Penti wrote:
                          Your code is correct...
                          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"
                            hitgon
                            Thanks Billy Verreynne for great explanation