11 Replies Latest reply: Jun 14, 2013 5:07 AM by Tom Petrus RSS

    Searching 2 years either side of a date field?

    Rambo79
      Hi

      In my query I would like the search to be able to search 2 years either side of a specified search date in my interactive report

      select
      ROWID,
      ID,
      COLUMN1,
      COLUMN2,
      TO_CHAR(INDDOB, 'YYYY') AS NEWYEAR
      from TABLENAME

      ORDER BY INDDOB asc

      The interactive report is also using custom Javascript to offer an autocomplete search as per this thread For VC - Using multiple Autocomplete on a page?

      So I am unsure how this would work by allowing the date results to include 2 years either side of the chosen date?
        • 1. Re: Searching 2 years either side of a date field?
          Rambo79
          any ideas if this is possible in APEX?
          • 2. Re: Searching 2 years either side of a date field?
            Stijn Van Raes
            Rambo you could create a datepicker item on your page as search field.

            And then change the query as follows:

            select
            ROWID,
            ID,
            COLUMN1,
            COLUMN2,
            TO_CHAR(INDDOB, 'YYYY') AS NEWYEAR
            from TABLENAME
            where INDDOB between add_months(to_date(:PX_DATEPICKER, 'DD-MM-YYYY'),-24) AND add_months(to_date(:PX_DATEPICKER, 'DD-MM-YYYY'),24)

            You could refresh your report using a dynamic action when the datepicker is filled in.

            Edited by: Stijn on 7-jun-2013 6:32
            • 3. Re: Searching 2 years either side of a date field?
              Rambo79
              Hi Stijn

              Many thanks, instead of using a date picker could I just use a text field, as I only want to search by year?

              If so what would the WHERE clause need to be changed to from your example?
              • 4. Re: Searching 2 years either side of a date field?
                Stijn Van Raes
                That's possible too, just change the format in the clause:


                where INDDOB between add_months(to_date(:PX_TEXTFIELD, 'YYYY'),-24) AND add_months(to_date(:PX_TEXTFIELD, 'YYYY'),24)


                Greetings
                • 5. Re: Searching 2 years either side of a date field?
                  Rambo79

                  Hi Thanks

                   

                  I have changed the query as you suggested but when viewing my interactive report it is now stating that 'Sorry no search items have been found' but when I take off the WHERE clause all of the records return which then allows me to refine the results searching by name, surname, date etc

                  • 6. Re: Searching 2 years either side of a date field?
                    Tyson Jouglet

                    Hey Rambo,

                     

                     

                     

                    What is the item name on your form?

                    What is the query?

                    What are you entering as input?

                    Did you verify that the bind variable names you are using are correct?

                    Did the item have a value?(if it was null then this query would not return anything)

                     

                     

                     

                    Please try to post the actual query when you have a question like this. It may reveal something that doesn't seem important that was not included in the post.

                     

                     

                     

                    Good Luck,

                    Tyson

                    • 7. Re: Searching 2 years either side of a date field?
                      Rambo79

                      Hi Tyson

                       

                      What is the item name on your form? 

                      YEARD

                       

                      What is the query?

                      select

                      ROWID,

                      ID,

                      INDSUB,

                      INDDOD,

                      INITCAP(INDSNAME) indsname,

                      INITCAP(INDFNAME) INDFNAME,

                      TO_CHAR(INDDOD, 'YYYY') AS NEWYEAR,

                      DELDOC from   RAMBO.TABLENAME

                      where INDDOD between add_months(to_date(:YEARD, 'YYYY'),-24) AND add_months(to_date(:YEARD, 'YYYY'),24)

                      ORDER BY INDDOD asc

                       

                      What are you entering as input?

                      I am entering the year in the text field which brings up the autocomplete - so for example 1996

                       

                       

                      Did the item have a value?(if it was null then this query would not return anything)

                      The item does not have a value, before I added the above WHERE clause it would bring back all results and then I would use the form fields in the interactive report to search and refine results

                      • 8. Re: Searching 2 years either side of a date field?
                        Rambo79

                        Hi all any ideas on getting around this I have tried the following in toad as an example but it's still not working.  I added 1970 as a default but in production I would just like the IA report to bring back all records as the form IA report loads and then as a user types in a date i.e. 1985 in the :YEARD field it will bring back results in the IA report from 1985 plus 2 years either side of this date

                         

                         

                         

                        where NEWYEAR between (DECODE(add_months(to_date(:YEARD, 'YYYY'),-24),NULL,'1970',:YEARD)) AND (DECODE(add_months(to_date(:NEWYEAR, 'YYYY'),24),NULL,'1970',:YEARD) )

                         

                        Would appreciate any Oracle experts to help me solve this problem and if it's even feasible?

                        • 9. Re: Searching 2 years either side of a date field?
                          Mike Kutz

                          Please stop using your derived NEWYEAR column.

                          You are doing a DATE range search.  As such, you must search by the DATE column INDDOB

                          Otherwise, the database can not take advantage of indexes.

                          Speaking of DATE columns..

                           

                          is INDDOB a DATE column?

                          If not... fix it.

                           

                           

                           

                          Next question, do you have any data that falls into the range that you are searching?

                          select to_char( INDDOD, 'YYYY' ) as year, count(*) N

                          from RAMBO.TABLENAME

                          group by to_char( INDDOD,'YYYY' )

                          order by year;

                           

                          If TOAD doesn't return anything, APEX can't either.

                           

                           

                          3rd: it sound like you really want to search for data between

                          1-jan-{2 years before} 00:00:01

                          to

                          31-dec-{2 years after} 23:59:59

                           

                          in that case,

                          where

                          inddod between

                            to_date( '00:00:01 1-JAN-' || :YEARD, 'HH24:MI:SS DD-MON-YYYY')

                            and

                            to_date( '23:59:59 31-dec-' || :YEARD, 'HH25:MI:SS DD-MON-YYYY')

                           

                           

                          Next question:

                          Are you sure the item name is :YEARD???

                          I'm used to seeing ":P26_SEARCH_YEAR"

                           

                           

                          Finally, are you sure you typed in a valid four digit year?

                          Make :YEARD a number type with a minimum value of 1900 (adjust this according to your data)

                          • 10. Re: Searching 2 years either side of a date field?
                            Rambo79

                            Mike many thanks for this

                             

                            INDDOB is a date field in the DB and the item name is :YEARD

                             

                            Using the following WHERE clause and a default vale of 1930 in the :YEARD item name is returning the correct result i.e. 1930 plus records from 1928,1929,1931 and 1932

                             

                            select

                            ROWID,

                            ID,

                            INDSUB,

                            INDDOD,

                            INDREG,

                            INDENT,

                            INDDOD,

                            INITCAP(INDSNAME) indsname,

                            INITCAP(INDFNAME) INDFNAME,

                            TO_CHAR(INDDOD, 'YYYY') AS NEWYEAR,

                            DELDOC from   RAMBO.TABLENAME

                            WHERE to_char(INDDOD,'YYYY') between :YEARD -2 and :YEARD +2

                            ORDER BY INDDOD asc

                             

                            The problem is however when I try and change the search value in :YEARD from the default 1930 to for example 1950 (which is also in the db)

                             

                            When doing this it brings back no results? Could it be something to do with the associated code below which provides an autocomplete feature and search of the IA report as you type in the field name.

                             

                            In the :YEARD item under label and HTML table Cell attributes has the following

                            onkeydown="if($f_Enter(event)){srch3($v('YEARD')); return false;}"

                             

                            and under the list of values in the definition for :YEARD is

                            select distinct TO_CHAR(INDDOD, 'YYYY') from RAMBO.TABLENAME order by 1

                             

                            Also under page and Javascript is the following

                            function srch2(pItem, pColumn) {  

                               $.post('wwv_flow.show',

                                      {"p_request"          : 'APXWGT',

                                      "p_widget_action"     : 'FILTER',

                                      "p_widget_action_mod" : 'ADD',

                                      "p_widget_mod"        : 'ACTION',

                                      "p_widget_name"       : 'worksheet',

                                      "p_flow_id"           : $v('pFlowId'),

                                      "p_flow_step_id"      : $v('pFlowStepId'),

                                      "p_instance"          : $v('pInstance'),

                                      "x01"                 : $v('apexir_WORKSHEET_ID'),

                                      "x02"                 : $v('apexir_REPORT_ID'),

                                      "x03"                 : pColumn,

                                      "f01"                 : ['COLUMN', pColumn, '=', $v(pItem), null, 'minutes']

                                     },

                                     function(data){

                                        //when the filter has been successfully applied the report has to be refreshed

                                        if(data=="true"){

                                           gReport.pull();

                                        };

                                      }

                                     );

                             

                               //clear the item

                               $s(pItem,'');

                            };

                             

                            $(document).ready(function(){

                               //hide IR toolbar

                               $('#apexir_TOOLBAR').hide();

                             

                               $('#P4_AC').bind("result", function(){   

                                  //on selecting a value then fire the search function

                                  srch2(this, 'INDSNAME');

                               });

                             

                               // ac item P4_AC2

                               $('#P4_AC2').bind("result", function(){   

                                  srch2(this, 'INDFNAME');

                               });

                             

                               // ac item YEARD

                               $('#YEARD').bind("result", function(){

                                  srch2(this, 'NEWYEAR');

                               });

                             

                             

                            // ac item REG

                               $('#REG').bind("result", function(){

                                  srch2(this, 'INDREG');

                               });

                             

                             

                            // ac item ENTNO

                               $('#ENTNO').bind("result", function(){

                                  srch2(this, 'INDENT');

                               });

                            });

                            • 11. Re: Searching 2 years either side of a date field?
                              Tom Petrus

                              You should really have mentioned this code from the start. The solutions everybody has been working on simply can't work.

                              You edited the where clause in, which would work when the session state for the YEARD item would at least be changed. You can refresh the region as many times as you want, but if the value doesn't change then the output will neither. You can always verify session state of the item by clicking "session" on your developer bar. It'll be empty while it shouldn't.

                              performing the srch2 javascript function will add a filter on your interactive report. An actual filter, the sort you'd see when you would apply one through actions > filter. Do you get to see such a filter added? If not then the javascript code will fire an ajax call which will return unsuccessfully. If you do see a filter, you should note that it is of the wrong type: it will be a equal filterm ie "year = 1995" and not a range filter, simply because that is what you tell it to apply through the ajax call's parameters.

                              "f01"                 : ['COLUMN', pColumn, '=', $v(pItem), null, 'minutes']

                               

                              You'll have to decide how you want to solve this. Both options are not mutually exclusive, but if you're struggling with this right now then you might want to focus on just one problem.

                              A: go with the ajax route and apply an actual IR filter when you select an option. This is an advantage if you want to have the user remain in control of the filter and want to allow them to remove it. It also is a good visual indicator of what has been filtered.

                              B: go with the addition in the where clause. This will reduce the data set while not having been applied as an IR filter.