4 Replies Latest reply on Aug 10, 2018 12:03 PM by Confused of Apex

    Tree versus IG - how they translate Date Picker output in the where clause

    Confused of Apex

      Hi 

       

      Background:  I am a long time PL/SQL developer who has recently become 'confused of Apex'! - using Apex 5.1.2

       

      While doing some experimentation with a Tree region some rather confusing things have come up which I wonder if anyone can shed a light on...

       

      Scenario:  A page with 2 date pickers start_date and end_date, a Tree, and an IG - Almost the same SQL in the Tree and IG queries, and that is the issue!

       

      I have assumed that the data provided by the date pickers is actually in string/text format and not an Oracle date type.  This has been borne out by doing various conversions on the output data in PL/SQL to see which works and which excepts.  The weird thing is that the IG and the Tree seem to treat the data differently! The where clause in the IG is:

       

      --Note Date picker format set to 'DD/MM/YYYY'

      and ts_entry between to_date(:P210_START_DATE, 'DD/MM/YYYY')

                          and to_date(:P210_END_DATE, 'DD/MM/YYYY')

       

      Which works fine and produces the correct rows (one note in case it is important is that ts_entry is actually a timestamp).  However the where clause that works in the Tree query is:

       

      and ts_entry between :P210_START_DATE

                                                 and :P210_END_DATE

       

      Converting the Tree to the same where clause as the IG results in no rows returned (or at least not displayed), putting the Tree version in the IG returns an Ajax exception:

       

      Ajax call returned server error ORA-01843: not a valid month for

       

      Which would be expected when feeding character data to an expression wanting a string.  So it seems the 2 queries are treating the same data in different ways!  Anyone know why?

       

      As a secondary note to this, I am not using an Oracle Hierarchical query for the tree, the data are already in  Hierarchical format, but as stated above, the data does display correctly when the date picker output is treated as a date and not as a character string.

       

       

       

      And a final query, adding a DA to call refresh on the Tree when either of the date pickers has no affect, another DA on the same event handlers works fine to refresh the IG?

       

      Help gratefully received!

       

      Bob

       

        • 1. Re: Tree versus IG - how they translate Date Picker output in the where clause
          Neil Clare

          what is the Application Date Format (found under Globalization Attributes on the Shared Components page)?

          • 2. Re: Tree versus IG - how they translate Date Picker output in the where clause
            fac586

            user7396793 wrote:

            Please update your forum profile with a recognisable username instead of "user7396793": Video tutorial how to change nickname available

             

            When you ask a question, always include the information detailed in these guidelines so we can provide version-, environment-, and application-specific advice.

            Background: I am a long time PL/SQL developer who has recently become 'confused of Apex'! - using Apex 5.1.2

             

            While doing some experimentation with a Tree region some rather confusing things have come up which I wonder if anyone can shed a light on...

             

            Scenario: A page with 2 date pickers start_date and end_date, a Tree, and an IG - Almost the same SQL in the Tree and IG queries, and that is the issue!

             

            I have assumed that the data provided by the date pickers is actually in string/text format and not an Oracle date type. This has been borne out by doing various conversions on the output data in PL/SQL to see which works and which excepts. The weird thing is that the IG and the Tree seem to treat the data differently! The where clause in the IG is:

             

            --Note Date picker format set to 'DD/MM/YYYY'

            and ts_entry between to_date(:P210_START_DATE, 'DD/MM/YYYY')

            and to_date(:P210_END_DATE, 'DD/MM/YYYY')

             

            Which works fine and produces the correct rows (one note in case it is important is that ts_entry is actually a timestamp). However the where clause that works in the Tree query is:

             

            and ts_entry between :P210_START_DATE

            and :P210_END_DATE

             

            Converting the Tree to the same where clause as the IG results in no rows returned (or at least not displayed), putting the Tree version in the IG returns an Ajax exception:

             

            Ajax call returned server error ORA-01843: not a valid month for

             

            Which would be expected when feeding character data to an expression wanting a string. So it seems the 2 queries are treating the same data in different ways! Anyone know why?

             

            As a secondary note to this, I am not using an Oracle Hierarchical query for the tree, the data are already in Hierarchical format, but as stated above, the data does display correctly when the date picker output is treated as a date and not as a character string.

             

             

             

            And a final query, adding a DA to call refresh on the Tree when either of the date pickers has no affect, another DA on the same event handlers works fine to refresh the IG?

            All APEX session state values are stored as character strings, so they must be explicitly converted to the correct data type when referenced as NUMBER, DATE, TIMESTAMP, or RAW values in SQL and PL/SQL. Since the database column type is a TIMESTAMP, convert the page item values in the filter predicates to exactly the same type (TIMESTAMP, TIMESTAMP WITH TIME ZONE, or TIMESTAMP WITH LOCAL TIME ZONE).

             

            Test the queries in SQL Developer or the APEX SQL Workshop. If the tests return the expected results, but the tree region does not, we'll need to look at what happens to tree data sources behind the scenes.

             

            Also check that the required values are correctly set in session state at the time that the regions are rendered or refreshed.

             

            • 3. Re: Tree versus IG - how they translate Date Picker output in the where clause
              Confused of Apex

              Hi Niel

               

              For this app I don't have them set...

               

              However, I do not expect this to be a problem, it was something that I thought about when I added the data pickers.  Both date pickers have a format mask set as 'DD/MM/YYYY'.  Also, because I was worried about this facet, I print them to the console each time they change, and they appear in the correct format.

               

              Thanks

               

              Bob

              • 4. Re: Tree versus IG - how they translate Date Picker output in the where clause
                Confused of Apex

                Hi fac586

                 

                hmm, did try to change it, but was denied for some reason that I was not in the mood to fathom yesterday...

                 

                First, a bit of news about this following on from yesterday, while the above construct still does not work, the following does:-

                 

                 

                   and ts_entry >= to_date(:P120_START_DATE, 'DD/MM/YYYY')

                   and ts_entry <= to_date(:P120_END_DATE, 'DD/MM/YYYY')

                 

                Which is somewhat weird! Yes I have tested these queries quite extensively, as I said at the top, I have been a PL/SQL dev for a few years now... OK, I hate SQL Developer with a passion, but I do use SQL Navigator which better suits my needs and temperament!

                 

                I did wonder whether what I was seeing was a refusal of the tree to draw for some reason so I checked the data being returned from the 3 queries quite carefully to make sure they started with the same row (worried it might present a wrong STATUS).  Actually, the biggest surprise, is that the data returned by the tree view with no to_date in the between clause worked at all, this was only tried as I was beginning to wonder if Apex did something 'clever' behind the scenes and it was being presented as a date type and not a string, and the data in the tree actually looked correct for the range! Also, just in case, I tried converting to timestamp, this made no difference as I expected, Oracle coerces between dates and timestamps pretty well...

                 

                How to go about getting at the Tree data?  I have only seen it chucked to the console when there is an error? - This would be very useful knowledge...

                 

                Thanks for your reply

                 

                Bob