Automatic Report Query Read Timeout

Ignition
,
1

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.

2

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")

3

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!

4

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)

5

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:

image.png804×461 25.2 KB

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!

6

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

7

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

8

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

9

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.

10

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

11

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
12

I will try that and let you know. Thanks

13

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

14

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

15

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!!!