9 Replies Latest reply on Jan 15, 2019 11:56 AM by 3829614

    Apex date picker not sorting out my data

    3829614

      Hi all,

      I'm using apex to design a report. I want to be able to choose the data I want to view based on the date chosen in the date picker.

      I have two date pickers. one for start_date and the other for end_date. But the data is not showing for the dates chosen in the date picker.

      It still shows all the data in the table.

      the code is below.

       

       

      select deal_id, Client_name,Client_Account,Type_of_account ,ready_date

      from vw_transaction

      WHERE Type_of_account like'%'||:P3_TOA||'%' 

      AND ready_date BETWEEN NVL(:P3_Start_Date,'1-JAN-1900') AND NVL(:P3_End_Date,'1-JAN-2099')

      OR (:P3_Start_Date is null and :P3_End_Date is null);

       

       

      Please help

        • 1. Re: Apex date picker not sorting out my data
          Jim-D

          Welcome!  The Community Feedback space is intended for discussions about the Oracle Community website, not for product-specific questions.

          There are tips on how to find the correct space on the Getting Started  page, which also has instructions on how to change your display name so you're not just a number.

          In the ACTIONS box on the top right of your post, you should be able to click on "Move", and then specify the correct space - such as Oracle Application Express (APEX) - so the right experts can see your question.

          Thanks and good luck!

          MOSC Move.png

          • 2. Re: Apex date picker not sorting out my data
            PMON

            You need to make sure that your item values are being commited to your session for that to work.  The easiest way to accomplish this would be to create an onChange dynamic action that submits your page.

             

            Hope this helps

            • 3. Re: Apex date picker not sorting out my data
              Mint-Innit

              Well it depends how you're triggering your report to refresh, but at the very least make sure all page items used in your report query are listed in the 'Page Items To Submit' attribute for the report.

               

              Also, dates in page items are stored as text representations not as date types. Therefore if you compare them to data in a table which IS held in a date type, you need to use something like to_date() to convert them as part of the comparison. Otherwise your results might be... unexpected.

               

              Cheers.

              • 4. Re: Apex date picker not sorting out my data
                3829614

                I have a submit button that has been set to submit the parameters so that it refreshes the report.

                • 5. Re: Apex date picker not sorting out my data
                  3829614

                  Yes the dates are stored in as date data type in the table ..... how do I use the TO_DATE function on the query above. .... I have tried some examples I saw online but none seem to be working.

                  • 6. Re: Apex date picker not sorting out my data
                    Mint-Innit

                    https://docs.oracle.com/database/121/SQLRF/functions219.htm#SQLRF06132

                     

                    e.g.

                    select to_date('08-01-2019 13:45', 'DD-MM-YYYY HH24:MI') from dual;
                    

                     

                    will convert the string into a proper date type.

                     

                    Cheers.

                    • 7. Re: Apex date picker not sorting out my data
                      Sven W.

                      As others correctly commented the problem is probably that you compared string values and not date values.

                      There are several very common mistakes you should try to avoid.

                       

                      1) For example if you use month names like "JAN" then you also need to make sure the nls_date_language is set correctly. Not every language abbreviates the first month as "JAN". I prefer to avoid those language dependencies. For example by using "01". But since the value for the page items also needs to change, this format mask also needs to be added to the page item in apex.

                       

                      2) OR has a lower priority than AND. So your type_of_account filter ONLY works with the ready_date check, but not for the start_date/end_date check. Or to say it differently, if start_date is null AND end_date is null then the type_of_acount can be anything. Is this what you want?

                      (https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/About-SQL-Conditions.html#GUID-65B103FE-C00C-46A3-81… )

                       

                      3) A LIKE comparison usually can not use an index. An equalilty comparison can use an index. And additionally the optimizer can give a much better guess about the number of rowsd that this filter will use. Which will lead to better and more stable execution plans.

                      You should check if the filter Type_of_account LIKE '%XXX%' is need, or if you change that into an equality check.

                       

                      Here is how to modify the query (the blank before the items as P3_TOA needs to be removed. I only typed them, because otherwise the forum replaces that with an emoji .

                       

                      select deal_id, Client_name,Client_Account,Type_of_account ,ready_date

                      from vw_transaction

                      WHERE Type_of_account = : P3_TOA

                      AND (ready_date BETWEEN NVL(to_date(:P3_Start_Date,'DD-MM-RRRR'),to_date('01-01-1900'))

                                                            AND NVL(to_date(:P3_End_Date,'DD-MM-RRRR'),to_date('01-01-2099'))

                               OR (:P3_Start_Date is null and :P3_End_Date is null)

                               );

                      • 8. Re: Apex date picker not sorting out my data
                        3829614

                        I have tried your solution but it's still not sorting it out. .... I have found a way to do it in interactive report where I use date between function to help me select two different dates. how can I replicate that in my application.

                        • 9. Re: Apex date picker not sorting out my data
                          Jorgelina

                          If you are using an interactive report it won't let you sort your items in your report... when you add a "order by " clause it will fire out an error.

                          Unless...... you do something like this, and make sure that the default IR report is not sorted in any ways.

                           

                          select field1, field2, etc...

                          from   (select field1, field2, etc

                                     from   table

                                     where  field_date between date1 and date2

                                     order by field_Date desc)

                           

                          In this way, in an interactive report you can get your default data sorted out by the field you want, but this sort gets automatically overwritten as soon as the user selects another way to sort the data out, or if they save a private report with a different way of sorting the data.

                          Regards,

                           

                          Jorgelina

                          • 10. Re: Apex date picker not sorting out my data
                            Sven W.

                            I understood the phrase "data not sorting out" to be referring to data not filtering. I believe it was not ment as an ordering issue.

                             

                            @3829614 what you could do is to set a high debug level when running your working way using the interactive report.Then you will see in the debug, the exact query that apex executes against the database. This should include the between filter on the date column. You can consider to copy that logic to your own select.

                             

                            Best way would be to setup a small test case on apex.oracle.com. The problem looks like it can be solved very easily, but there are a lot of tiny dependencies that are hard to guess. For example at the moment, you never showed that your data actually has values that do fit the multiple conditions.

                            • 11. Re: Apex date picker not sorting out my data
                              3829614

                              Capture new.PNG

                              This is what I'm talking about ... Just that with my application, I only want to have the two date items. I will set the conditions in the background.