4 Replies Latest reply: Mar 20, 2013 7:36 AM by Ryansun-Oracle RSS

    Adding different values to a select list?

    Ryansun-Oracle
      4.2.1

      Hi There,

      I have three pages, home page which has a report with one Period filter - Values last 7 days and last 14 days. Period filter is :P1_PERIOD
      I have another page with the same report - values last 1 day, last 7 days, last 30 days and last quarter. Period filter is :P2_PERIOD

      The above two page shows the same reports based on the period selected in the respective pages.
      Page 1 
      Item           last 7 days          last 14 Days
      lights             10                      16
      Tail Lamp         2                      10
      
      Page 2
      
      Item              last 1 day              last 7 days                 last 30 days                   last quarter
      
      lights               1                         10                              20                               100
      .
      .
      Now I have a third page which is basically a details page which lists out the item details
      item_id              item_name                  Item_Purchase_date
      
      1                       Lights                             19-Mar-2013
      1                       Lights                              16-Mar-2013
      .
      .
      The counts on page 1 and page 2 are hyperlinked.

      My question is

      1. In page two the :P2_PERIOD has different select list options. so in page 3 I have an interactive report, When I run the query, I run it like
      select * from item_master where item_purchase_date between decode(:P2_period,1, sysdate-1, 2, sysdate-7,3, sysdate-30, 4, sysdate-90) and sysdate.
      However, I want to also do the same from page 1. Where when the user hits on the counts on 7, 14 days, it will take them to page three but the same report would be run for the 7 or 14 day period based on what user has selected in :P1_PERIOD.
      select * from item_master where item_purchase_date between decode(:P1_period,1, sysdate-7, 2, sysdate-14) and sysdate.
      Any advise on how to do this?

      Thanks,
      Ryan
        • 1. Re: Adding different values to a select list?
          Tauceef
          you can use CASE WHEN in where clause of your query like:

          case when :P2_PERIOD is not null then
          your code
          when :P1_PERIOD is not null then
          your code
          end

          This will work for you.

          Regards,
          Tauceef
          • 2. Re: Adding different values to a select list?
            fac586
            ryansun wrote:
            4.2.1

            Hi There,

            I have three pages, home page which has a report with one Period filter - Values last 7 days and last 14 days. Period filter is :P1_PERIOD
            I have another page with the same report - values last 1 day, last 7 days, last 30 days and last quarter. Period filter is :P2_PERIOD

            The above two page shows the same reports based on the period selected in the respective pages.
            Page 1 
            Item           last 7 days          last 14 Days
            lights             10                      16
            Tail Lamp         2                      10
            
            Page 2
            
            Item              last 1 day              last 7 days                 last 30 days                   last quarter
            
            lights               1                         10                              20                               100
            .
            .
            Now I have a third page which is basically a details page which lists out the item details
            item_id              item_name                  Item_Purchase_date
            
            1                       Lights                             19-Mar-2013
            1                       Lights                              16-Mar-2013
            .
            .
            The counts on page 1 and page 2 are hyperlinked.

            My question is

            1. In page two the :P2_PERIOD has different select list options. so in page 3 I have an interactive report, When I run the query, I run it like
            select * from item_master where item_purchase_date between decode(:P2_period,1, sysdate-1, 2, sysdate-7,3, sysdate-30, 4, sysdate-90) and sysdate.
            However, I want to also do the same from page 1. Where when the user hits on the counts on 7, 14 days, it will take them to page three but the same report would be run for the 7 or 14 day period based on what user has selected in :P1_PERIOD.
            select * from item_master where item_purchase_date between decode(:P1_period,1, sysdate-7, 2, sysdate-14) and sysdate.
            Any advise on how to do this?
            Take a different approach. Create a new P3_PERIOD_START item on page 3, compute the required date on pages 1 and 2, and pass the calculated value to page 3 in the links (using a URL-safe format like YYYYMMDD). Change the report query to
            select * from item_master where item_purchase_date between to_date(:p3_period_start, 'YYYYMMDD') and sysdate
            (Think carefully about the implications of using <tt>select *</tt> in production code. Unwanted side effects may occur if the table definition is changed.)
            • 3. Re: Adding different values to a select list?
              fac586
              Tauceef wrote:
              you can use CASE WHEN in where clause of your query like:

              case when :P2_PERIOD is not null then
              your code
              when :P1_PERIOD is not null then
              your code
              end

              This will work for you.
              Will it? What if neither is null? And, whilst it would be possible to clear cache for the other page value in the links, there may be other reasons for the application to retain that page's session state...
              • 4. Re: Adding different values to a select list?
                Ryansun-Oracle
                Thanks Fac586, You just confirmed what I was planning on doing.

                Yes, not using "select *" Just for this question, I put in that. :)

                Thanks!