Automatic Report Query Read Timeout

First off I hope I can explain this well enough for someone to understand. I apologize ahead of time.
I am trying to run a query to capture minute data for a daily report that needs to run automatically. The report is pulling from a database that has 20 different UNIT_ID’s and I only need the ID 2154. I need to sort by time stamp but, I need to run this report daily.
However using this code:
SELECT CEMS_DATA_MIN.DATE_TIME,
CEMS_DATA_MIN.UNIT_ID,
CEMS_DATA_MIN.DATA32,
CEMS_DATA_MIN.DATA30
FROM CEMS_DATA_MIN
WHERE CEMS_DATA_MIN.UNIT_ID = 2154
ORDER BY CEMS_DATA_MIN.DATE_TIME

Both the Preview and the DB query browser run fine. However, the DB has been gathering minute data for over 5 years now so it is rather large. When I run this as a report it times out. I figure it is because the report is pulling from all 5 years worth of data. I have the schedule configured to run between the StartDate and the EndDate and I have it configured to run once daily at 11:59 PM but, this does not help either. I tried the example below but when I run it I get "Error running query: Select… ORA-01008: not all variables bound.
SELECT CEMS_DATA_MIN.DATE_TIME,
CEMS_DATA_MIN.UNIT_ID,
CEMS_DATA_MIN.DATA32,
CEMS_DATA_MIN.DATA30
FROM CEMS_DATA_MIN
WHERE CEMS_DATA_MIN.DATE_TIME BETWEEN ? AND ? AND CEMS_DATA_MIN.UNIT_ID = 2154
ORDER BY CEMS_DATA_MIN.DATE_TIME

Parameter 1 {StartDate} and Parameter 2 {EndDate}
I have tried many different variations on the above code but, I always get an error of one kind or another. Any help on this would be greatly appreciated.

What do you have the parameters default values set to?
Use the second query and then to get them to use a valid datetime when scheduled to run put their default value to something like:

StartDate:
dateFormat(dateArithmetic(now(), -1, “day”),“MM/dd/yyyy 00:00:00”)

EndDate:
dateFormat(dateArithmetic(now(), -1, “day”),“MM/dd/yyyy 23:59:59”)

Sorry, I should have stated.
StartDate:
dateFormat(dateArithmetic(now(), -1, “day”),“MM/DD/YYYY 00:00:00”)

EndDate:
dateFormat(now(),“MM/DD/YYYY 23:59:59”)

Thanks for the quick reply!

Your parameters will pull 2 days worth of data, but that shouldn’t be an issue.
Can you post some example data from the table?

Also, it is better to use >= and <= on date criteria instead of between

WHERE CEMS_DATA_MIN.DATE_TIME >= ? AND CEMS_DATA_MIN.DATE_TIME <= ? AND (CEMS_DATA_MIN.UNIT_ID = 2154)

I tried using the >=? and <=?, first with the same results. The BETWEEN was just the setup I had when I copied and pasted to the forum.

here is the sample I get when I use the configuration I have at the top of my original post. When I run it through the DB Query Browser I get this:

It works fine but as you can see the data is from 2014 which is when the DB was created. I am limiting the query to 1000 rows and it works up to 20000 rows. However any higher than that and I get a read timeout. I want to capture the data in the report one 24 hour period at a time. If i put in the CEMS_DATA_MIN.DATE_TIME BETWEEN ? AND ? AND section of the code I get the error “not all variables bound”. This is the case as well if I use the >= ? AND <= ? format. Thanks again for the assistance!

Add DESC to the end of the order by to get the latest data.
And in the Database browser surround the dates with single quotes to test

WHERE CEMS_DATA_MIN.DATE_TIME >= ‘10/5/2017 00:00:00’ AND CEMS_DATA_MIN.DATE_TIME <= ‘10/5/2017 23:59:59’ AND (CEMS_DATA_MIN.UNIT_ID = 2154)
ORDER BY CEMS_DATA_MIN.DATE_TIME DESC

Also worth noting here that Oracle is shockingly bad at ORDER BY and LIMIT happening at the same time, which could be root of your error:
http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html

Do you have an index on UNIT_ID and DATE_TIME?
If the data is inserted sequentially, you may not need the order by.

I think I am trying to get too fancy for this report. I know that there are 1440 mins in a 24 hour period. Each minute is one row so, how would I set it up to look at just the last 1440 rows from the time that the report is run. I have the report set to run everyday at midnight so 1440 rows will give me all the data from the previous day if I use the DESC.

Thanks again for all the help on this, I really do appreciate it.

Happy Monday all! So, how do I partition my auto generated reports to only return the last 1440 rows from the run time of the auto generation. I hate to be a bother but, I am sort of in a time crunch and I will be eternally grateful for help on this!!

Thanks

I believe it should be something along these lines, but I’m not the best at Oracle syntax.

SELECT *
FROM (your ordered query) alias_name
WHERE rownum <= Rows_to_return
ORDER BY rownum

I will try that and let you know. Thanks

It seems that I am having a very difficult time understanding what I am doing this morning. I need to get the following code to generate a report that ONLY pulls the last 1440 rows. The report is from an ACCESS DB if that makes any difference. I tried using the rownum like lrose suggested however, I could not get it to work. I know that I am being a pain but, I am struggling and I appreciate the time and effort that you all are putting in to try and help me. I am trying to learn this SQL stuff on the fly :frowning: . Will the code the way I have it written allow for limiting the query to just 1440 rows with the addition of other parameters? Or do I need to look at different approaches? Thanks again for the help!!!

SELECT CEMS_DATA_MIN.DATE_TIME,
CEMS_DATA_MIN.UNIT_ID,
CEMS_DATA_MIN.DATA32,
CEMS_DATA_MIN.DATA30
FROM CEMS_DATA_MIN
WHERE CEMS_DATA_MIN.UNIT_ID = 2154
ORDER BY CEMS_DATA_MIN.DATE_TIME

Try the following in the Query Browser:

SELECT TOP 1440 CEMS_DATA_MIN.DATE_TIME,
CEMS_DATA_MIN.UNIT_ID,
CEMS_DATA_MIN.DATA32,
CEMS_DATA_MIN.DATA30
FROM CEMS_DATA_MIN
WHERE CEMS_DATA_MIN.UNIT_ID = 2154
ORDER BY CEMS_DATA_MIN.DATE_TIME DESC

Assuming that this really is a (Microsoft) Access DB that should work. Otherwise, if it is truly an Oracle DB then the following should work:

SELECT CEMS_DATA_MIN.DATE_TIME,
CEMS_DATA_MIN.UNIT_ID,
CEMS_DATA_MIN.DATA32,
CEMS_DATA_MIN.DATA30
FROM CEMS_DATA_MIN
WHERE CEMS_DATA_MIN.UNIT_ID = 2154 AND ROWNUM <= 1440
ORDER BY CEMS_DATA_MIN.DATE_TIME DESC

What I’m using for reference https://www.w3schools.com/sql/sql_top.asp

Thanks for the help. I was using that same site for my references too. It was not working and that is why I was having such a hard time. Turns out, once I deleted the table in the report designer and started fresh everything worked out with the rownum <= 1440. I was running around in circles with this and it turns out I had it correct all along the darn table was the culprit! Thank you all for your help!!!