5 Replies Latest reply: Mar 8, 2014 9:53 PM by NuhaArif RSS

    Multiple conditions

    NuhaArif

      i have a table with job statuses as open or close , if i pass the value open , only those records with status as open will be filtered, similarly when i choose close only records of job_status with close will be filtered, i can do this by passing open or close as parameters but when i dont pass any values , then all the records of ot_job_stat needs to be displayed but when i am adding multiple conditions to the above query , its not giving me correct results.Like i added date column to the table and i want to filter data based on multiple ranges like within the time span what job_types i want , but the query is yielding wrong results.

      {code}

       

      CREATE TABLE OT_JOB_STAT ( JOB_NO VARCHAR2(12),JOB_STATUS VARCHAR2(12),JOB_EQUIP VARCHAR2(12),JOB_TYPE VARCHAR2(12),JOB_DATE DATE);

       

      insert into ot_job_stat ( JOB_NO ,JOB_STATUS ,JOB_EQUIP ,JOB_TYPE,JOB_DATE ) values ('0001','Open','CNC1','Prev',TO_DATE('01/01/2014'));

       

      insert into ot_job_stat ( JOB_NO ,JOB_STATUS ,JOB_EQUIP ,JOB_TYPE , JOB_DATE ) values ('0002','Close','CNC1','Brk',TO_DATE('01/01/2014'));

       

      insert into ot_job_stat ( JOB_NO ,JOB_STATUS ,JOB_EQUIP ,JOB_TYPE , JOB_DATE ) values ('0003','Close','CNC2','Prev',TO_DATE('02/02/2014'));

       

      select * from ot_job_stat

      WHERE

        JOB_DATE between TO_DATE(:F_JOB_DT,'DD/MM/RRRR') AND TO_DATE(:T_JOB_DT,'DD/MM/RRRR')

        and job_type in :job_type or :job_type is null 

       

       

      --I am passing job_type as null and :F_JOB_DT = '01/01/2014' AND :T_JOB_DT = '01/01/2014'

       

      --its getting me all the three records , but actually i dont want the record of 02/02/2014.

       

      --result is

       

       

      JOB_NO JOB_STATUS JOB_EQUIP JOB_TYPE JOB_DATE

      0002        Close                CNC1           Brk          1/1/2014

      0003        Close                 CNC2          Prev         2/2/2014

      0001         Open                 CNC1          Prev         1/1/2014

       

       

      {/code}

        • 2. Re: Multiple conditions
          Partha Sarathy S

          You mean this?

          SELECT * FROM OT_JOB_STAT

          WHERE (UPPER(JOB_STATUS)= :STATUS

          OR (:STATUS IS NULL AND 1=1))

          AND JOB_DATE between TO_DATE(:F_JOB_DT,'DD/MM/RRRR') AND TO_DATE(:T_JOB_DT,'DD/MM/RRRR');

           

          • 3. Re: Multiple conditions
            Turion

            select * from ot_job_stat

            WHERE (  JOB_DATE between TO_DATE(:F_JOB_DT,'DD/MM/RRRR') AND TO_DATE(:T_JOB_DT,'DD/MM/RRRR')

                              and job_type in :job_type )

                     or (:job_type is null)

            • 4. Re: Multiple conditions
              Frank Kulash

              Hi,

               

              If you need to use both AND and OR in a condition, always use parentheses to separate them, such that, outside of the parentheses, all the conjunctions are the same, either all ANDs or all ORs.  Partha did this correctly in reply #2, above.  You don't need any condition like "1 = 1", however, you can just say:

               

              SELECT  *

              FROM    OT_JOB_STAT

              WHERE   (    :STATUS  = UPPER (JOB_STATUS)

                      OR   :STATUS  IS NULL

                      )

              AND     JOB_DATE  between  TO_DATE (:F_JOB_DT, 'DD/MM/YYYY')

                                AND      TO_DATE (:T_JOB_DT, 'DD/MM/YYYY')

              ;


              Avoid 'RRRR' format; it invites 2-digit years, and 2-digit years invite trouble.

               

              What if :f_job_dt or :t_job_date is NULL? Perhaps you want something like:

               

              SELECT  *

              FROM    OT_JOB_STAT

              WHERE   (    :STATUS  = UPPER (JOB_STATUS)

                      OR   :STATUS  IS NULL

                      )

              AND     (    JOB_DATE   >= TO_DATE (:F_JOB_DT, 'DD/MM/YYYY')

                      OR   :F_JOB_DT  IS NULL

                      )

              AND     (    JOB_DATE   <= TO_DATE (:T_JOB_DT, 'DD/MM/YYYY')

                      OR   :T_JOB_DT  IS NULL

                      )

              ;

              Notice how all the conjunctions outside of parentheses are AND.  Whenever OR is used, it is inside parentheses, separated from the ANDs.

              • 5. Re: Multiple conditions
                NuhaArif

                thanks all of you , i think its all about conditional predicates, the order of execution i believe.