9 Replies Latest reply: Oct 21, 2013 4:58 AM by 942954 RSS

    Date Picker query showing "No Data Found", works in SQLPlus

    user12030043
      I have created 2 Date Picker items - P2_START_DATE and P2_END_DATE. I have set the format of these to 'DD-MON-YYYY'.

      This is the query I have to select the data based on the values from the date pickers:

      select sample_date,reading from meter_data where sample_date between to_date(:P2_START_DATE,'DD-MON-YYYY') and to_date(:P2_END_DATE,'DD-MON-YYYY') order by sample_date;

      The P2_START_DATE is '20-JAN-2011', the P3_END_DATE is '21-JAN-2011'.

      The query returns "No Data Found" when run in APEX, but when I run this in SQLPlus on the host I get data:


      select sample_date,reading from meter_data where sample_date between to_date('20-JAN-2011','DD-MON-YYYY') and to_date('21-JAN-2011','DD-MON-YYYY') order by sample_date

      20-JAN-2011 .39
      20-JAN-2011 .14
      20-JAN-2011 .14
      20-JAN-2011 .18
      21-JAN-2011 .13


      Can someone explain what I'm doing wrong? I have tried a few different formats, with no luck.

      TIA
        • 1. Re: Date Picker query showing "No Data Found", works in SQLPlus
          Little Foot-Oracle
          Hi,

          This might be silly, but just to know, how are you sending the values(submitting the values selected), and

          Have you tried the same in SQL Workshop(>> SQL Command) in apex, is it giving correct results ?

          If it is giving then the problem is with the values in date picker, it is not submitting the values.
          Make sure that the values are been submitted (try to create a text field and check on submit are you able to see the date in that new item).

          Try this select sample_date,reading from meter_data where to_date(sample_date,'DD-MON-YYYY') between to_date(:P2_START_DATE,'DD-MON-YYYY') and to_date(:P2_END_DATE,'DD-MON-YYYY') order by sample_date;

          if the problem still persists then it would be ideal to generate the same in apex.oracle.com so that it will be easy to solve.


          Regards,
          Little Foot

          Edited by: Little Foot on Sep 6, 2011 9:59 PM
          added a query, change the sample_date format as to be compatible with others
          • 2. Re: Date Picker query showing "No Data Found", works in SQLPlus
            user12030043
            Hi,

            the data is inserted via SQL Loader from a flat file. The SAMPLE_DATE data is in the format "21/01/2011 06:00:00 AM"

            the query "select sample_date,reading from meter_data where sample_date between to_date(:P2_START_DATE,'DD-MON-YYYY') and to_date(:P3_END_DATE,,'DD-MON-YYYY') order by sample_date;"

            works in SQLWorkshop in APEX (prompts for the 2 values, which I enter as '19-JAN-2011' and '21-JAN-2011').

            I tried the query "select sample_date,reading from meter_data where to_date(sample_date,'DD-MON-YYYY') between to_date(:P2_START_DATE,'DD-MON-YYYY') and to_date(:P2_END_DATE,'DD-MON-YYYY') order by sample_date;" in the APEX report but that returns "No Data Found" as well.

            Thanks
            • 3. Re: Date Picker query showing "No Data Found", works in SQLPlus
              user12030043
              I've created a skeleton application on apex.oracle.com if someone wants to help. I can send the connection details.
              • 4. Re: Date Picker query showing "No Data Found", works in SQLPlus
                Little Foot-Oracle
                Hi,

                workspace, username and password?

                Regards,
                Little Foot
                • 5. Re: Date Picker query showing "No Data Found", works in SQLPlus
                  user12030043
                  Hi,

                  these are the details - workspace= meter_data
                  username = andy.horne@gmail.com
                  pass = wednesday

                  the username / password for the application is the same.

                  Thanks

                  Edited by: user12030043 on Sep 7, 2011 2:16 PM
                  • 6. Re: Date Picker query showing "No Data Found", works in SQLPlus
                    jwellsnh
                    Please update your handle to something more personal.

                    Looked at your application and found several issues. Made a copy of Page 2 (Page 4); it is now working.

                    Issues found on Page 2:
                    select null link, SAMPLE_DATE label, READING value1
                    from  "METER_DATA"."METER_READINGS"
                    had no Where clause; lacking a Between statement; added or replaced the Where clause on Page 4 to your original post
                    select null link, SAMPLE_DATE label, READING value1
                    FROM   meter_readings
                    WHERE  sample_date BETWEEN To_date(:P4_START_DATE, 'DD-MON-YYYY') AND To_date(
                                               :P4_END_DATE, 'DD-MON-YYYY')
                    ORDER  BY sample_date;
                    Button named; Submit Date Range redirected the application to Page 3; deleted that button and added a button Submit that submitted the page and rendered the chart

                    Chart Series maximum rows was set to 50 rows; I bumped the value up to 500 so as to pull the full range of dates in your table meter_readings

                    Jeff
                    • 7. Re: Date Picker query showing "No Data Found", works in SQLPlus
                      Alex Cross
                      Hi there,

                      I log on in your account and I add a testing region in page 3..

                      I just revised your query to this:

                      select sample_date,reading from meter_readings

                      where sample_date between :P2_START_DATE and :P2_END_DATE


                      And its working, please through it..

                      Hope this will help... =)

                      Regards,
                      shynn,,

                      Edited by: shynn on Sep 7, 2011 11:35 PM

                      Edited by: shynn on Sep 7, 2011 11:38 PM
                      • 8. Re: Date Picker query showing "No Data Found", works in SQLPlus
                        user12030043
                        Hi,

                        many thanks for the help from everyone, sorry for the issues, I can't think why I did that, must have been late.

                        I can't see what the difference is between my query and yours. This is my where clause from my original post:

                        where sample_date between to_date(:P2_START_DATE,'DD-MON-YYYY') and to_date(:P2_END_DATE,'DD-MON-YYYY') order by sample_date;


                        This is yours:

                        WHERE sample_date BETWEEN To_date(:P4_START_DATE, 'DD-MON-YYYY') AND To_date(:P4_END_DATE, 'DD-MON-YYYY') ORDER BY sample_date;

                        They look the same to me.

                        Thanks for the help.

                        Edit: I've just had a closer look at the app - someone has been fiddling. That's the problem with posting the details in a public forum.

                        Edited by: user12030043 on Sep 9, 2011 3:36 AM
                        • 9. Re: Date Picker query showing "No Data Found", works in SQLPlus
                          942954

                          Hi Jeff,

                          My Requirement is also looks like same, but am not able figure it out the reason why am getting no data found message in my chart(report).

                          Requirement:

                          I have a page, where two date picker(html5) items let say P30_FROM_DATE, P30_TO_DATE are there, apart from this i have one button as well,

                          when the user select the from and to date from the date picker, and as soon as click on the button (SHOW), need to display the 2D Stacked Bar Char.

                          so to achieve this, i have created a blank page, under region i have created a chart (2D Stacked Bar Chart), under items--> i have created two date picker items and one button.

                          my query is

                          SELECT NULL LINK,

                                 FLOW_STATUS_CODE LABEL,

                                 COUNT(ORDER_NUMBER) VALUE

                            FROM

                                 OE_ORDER_HEADERS_ALL

                          WHERE

                                 CREATION_DATE

                                 BETWEEN TO_DATE(:P30_FROM_DATE,'DD-MON-YYYY') AND TO_DATE(:P30_TO_DATE,'DD-MON-YYYY')

                          GROUP BY FLOW_STATUS_CODE;

                           

                          Issue:

                           

                          Once i select the From and To Date, when am click on Show button, am not able to see as desired chart, instead am getting no data found message in the chart location.

                           

                          Can you please tell me how to do this.

                          i would like to know that, how do i capture those two date item values in my query, and how do i initiate my report when am pressing button.

                           

                          please revert back to my below mail.

                           

                          Regards

                          Santhosh Kumar. K

                          skandakatla@bodhtree.com