2 Replies Latest reply on Nov 14, 2017 5:49 PM by Portia2017

    Generate Interactive Report Based on Filter Conditions

    Portia2017

      Hi everyone,

      I have been developing an application with Oracle APEX version 5.1. I'm trying to generate an interactive report based on the filter conditions I created.

      Let's assume the information is in Page 1. What I want to achieve is:

      if p1_displayoption = 'deptno', the interactive report shows records whose department number is p1_deptno

      if p1_displayoption = 'tp', the interactive report shows records whose start date and finish date are between p1_startdate and p1_finishdate (that is where I have problem)

      if p1_displayoption = 'all', the interactive report shows all records

       

      I have tried decode and case. The problem is both functions only return one row, however, there will be more than one STARTDATE  and FINISHDATE in the table lie between P1_STARTDATE and P1_FINISHDATE.

       

      My code is:

       

      Select deptno, startdate, finishdate......

      from tablename

      where deptno = decode(p1_displayoption, 'deptno', p1_deptno,

                                                                          'tp', deptno,

                                                                          'all' deptno,

                                                                          null)

      and startdate = decode(p1_displayoption, 'deptno', startdate,

                                                                         'tp', -- that's where the problem is. What I want to do is: startdate>=p1_startdate and startdate<=p1_finishdate

                                                                         'all', startdate,

                                                                         null)

      and finishdate = decode(p1_displayoption, 'deptno', finishdate,

                                                                         'tp', -- What I want to do is: finishdate>=p1_startdate and finishdate<=p1_finishdate

                                                                         'all', finishdate,

                                                                         null)

       

      I really appreciate the time and help!

        • 1. Re: Generate Interactive Report Based on Filter Conditions
          fac586

          Portia2017 wrote:

           

          I have been developing an application with Oracle APEX version 5.1. I'm trying to generate an interactive report based on the filter conditions I created.

          Let's assume the information is in Page 1. What I want to achieve is:

          if p1_displayoption = 'deptno', the interactive report shows records whose department number is p1_deptno

          if p1_displayoption = 'tp', the interactive report shows records whose start date and finish date are between p1_startdate and p1_finishdate (that is where I have problem)

          if p1_displayoption = 'all', the interactive report shows all records

           

          I have tried decode and case. The problem is both functions only return one row, however, there will be more than one STARTDATE and FINISHDATE in the table lie between P1_STARTDATE and P1_FINISHDATE.

           

          My code is:

           

          Select deptno, startdate, finishdate......

          from tablename

          where deptno = decode(p1_displayoption, 'deptno', p1_deptno,

          'tp', deptno,

          'all' deptno,

          null)

          and startdate = decode(p1_displayoption, 'deptno', startdate,

          'tp', -- that's where the problem is. What I want to do is: startdate>=p1_startdate and startdate<=p1_finishdate

          'all', startdate,

          null)

          and finishdate = decode(p1_displayoption, 'deptno', finishdate,

          'tp', -- What I want to do is: finishdate>=p1_startdate and finishdate<=p1_finishdate

          'all', finishdate,

          null)

           

          I really appreciate the time and help!

          Use this approach to ensure that the optimizer can utilise available indexes or partitions:

           

          select
              deptno
            , startdate
            , finishdate
            , ...
          from
              tablename
          where
              :p1_option = 'deptno'
          and deptno = :p1_deptno
          union all
          select
              deptno
            , startdate
            , finishdate
            , ...
          from
              tablename
          where
              :p1_option = 'tp'
          and startdate <= to_date(:p1_finishdate, 'yyyy-mm-dd')
          and coalesce(finishdate, date '9999-12-31') >= to_date(:p1_startdate, 'yyyy-mm-dd')
          union all
          select
              deptno
            , startdate
            , finishdate
            , ...
          from
              tablename
          where
              :p1_option = 'all'
          

           

          Use the coalesce expression if tablename.finishdate can be null.

          1 person found this helpful
          • 2. Re: Generate Interactive Report Based on Filter Conditions
            Portia2017

            Thank you so muchfac586!!!! It works I have been stuck on this problem for days... You are a life saver!!!!