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.
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
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.
I've created a skeleton application on apex.oracle.com if someone wants to help. I can send the connection details.
workspace, username and password?
these are the details - workspace= meter_data
username = firstname.lastname@example.org
pass = wednesday
the username / password for the application is the same.
Edited by: user12030043 on Sep 7, 2011 2:16 PM
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:
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_DATA"."METER_READINGS"
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
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;
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
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... =)
Edited by: shynn on Sep 7, 2011 11:35 PM
Edited by: shynn on Sep 7, 2011 11:38 PM
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
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).
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,
BETWEEN TO_DATE(:P30_FROM_DATE,'DD-MON-YYYY') AND TO_DATE(:P30_TO_DATE,'DD-MON-YYYY')
GROUP BY FLOW_STATUS_CODE;
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.
Santhosh Kumar. K