# Automatic Report Query Read Timeout

**URL:** <https://forum.inductiveautomation.com/t/automatic-report-query-read-timeout/15593>\
**Category:** Ignition\
**Tags:** ignition79, database\
**Created:** [October 6, 2017, 2:31pm UTC](https://forum.inductiveautomation.com/t/automatic-report-query-read-timeout/15593 "2017-10-06T14:31:15Z")\
**Posts on this page:** 15\
**Page:** 1

<div class="post-metadata">

**Author:** ![gcampbell](https://avatars.discourse-cdn.com/v4/letter/g/3ab097/32.png) [@gcampbell](https://forum.inductiveautomation.com/u/gcampbell)\
**Post date:** [October 6, 2017, 2:31pm UTC](https://forum.inductiveautomation.com/t/automatic-report-query-read-timeout/15593/1 "2017-10-06T14:31:15Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![MMaynard](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mmaynard/32/3366_2.png) [@MMaynard](https://forum.inductiveautomation.com/u/MMaynard)\
**Post date:** [October 6, 2017, 3:02pm UTC](https://forum.inductiveautomation.com/t/automatic-report-query-read-timeout/15593/2 "2017-10-06T15:02:00Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![gcampbell](https://avatars.discourse-cdn.com/v4/letter/g/3ab097/32.png) [@gcampbell](https://forum.inductiveautomation.com/u/gcampbell)\
**Post date:** [October 6, 2017, 3:24pm UTC](https://forum.inductiveautomation.com/t/automatic-report-query-read-timeout/15593/3 "2017-10-06T15:24:30Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![MMaynard](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mmaynard/32/3366_2.png) [@MMaynard](https://forum.inductiveautomation.com/u/MMaynard)\
**Post date:** [October 6, 2017, 3:51pm UTC](https://forum.inductiveautomation.com/t/automatic-report-query-read-timeout/15593/4 "2017-10-06T15:51:47Z")

</div>

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)

---

<div class="post-metadata">

**Author:** ![gcampbell](https://avatars.discourse-cdn.com/v4/letter/g/3ab097/32.png) [@gcampbell](https://forum.inductiveautomation.com/u/gcampbell)\
**Post date:** [October 6, 2017, 4:05pm UTC](https://forum.inductiveautomation.com/t/automatic-report-query-read-timeout/15593/5 "2017-10-06T16:05:43Z")

</div>

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:

 ![](https://us1.discourse-cdn.com/inductiveautomation/original/2X/2/2948de032261e61fe20bf86717b5a61dae904e0b.png)

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!

---

<div class="post-metadata">

**Author:** ![MMaynard](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mmaynard/32/3366_2.png) [@MMaynard](https://forum.inductiveautomation.com/u/MMaynard)\
**Post date:** [October 6, 2017, 4:14pm UTC](https://forum.inductiveautomation.com/t/automatic-report-query-read-timeout/15593/6 "2017-10-06T16:14:28Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [October 6, 2017, 4:19pm UTC](https://forum.inductiveautomation.com/t/automatic-report-query-read-timeout/15593/7 "2017-10-06T16:19:00Z")

</div>

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](http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html)

---

<div class="post-metadata">

**Author:** ![jpark](https://avatars.discourse-cdn.com/v4/letter/j/a88e57/32.png) [@jpark](https://forum.inductiveautomation.com/u/jpark)\
**Post date:** [October 6, 2017, 4:35pm UTC](https://forum.inductiveautomation.com/t/automatic-report-query-read-timeout/15593/8 "2017-10-06T16:35:34Z")

</div>

Do you have an index on UNIT\_ID and DATE\_TIME?  
If the data is inserted sequentially, you may not need the order by.

---

<div class="post-metadata">

**Author:** ![gcampbell](https://avatars.discourse-cdn.com/v4/letter/g/3ab097/32.png) [@gcampbell](https://forum.inductiveautomation.com/u/gcampbell)\
**Post date:** [October 6, 2017, 4:52pm UTC](https://forum.inductiveautomation.com/t/automatic-report-query-read-timeout/15593/9 "2017-10-06T16:52:07Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![gcampbell](https://avatars.discourse-cdn.com/v4/letter/g/3ab097/32.png) [@gcampbell](https://forum.inductiveautomation.com/u/gcampbell)\
**Post date:** [October 9, 2017, 1:30pm UTC](https://forum.inductiveautomation.com/t/automatic-report-query-read-timeout/15593/10 "2017-10-09T13:30:28Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [October 9, 2017, 2:04pm UTC](https://forum.inductiveautomation.com/t/automatic-report-query-read-timeout/15593/11 "2017-10-09T14:04:19Z")

</div>

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

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

```

---

<div class="post-metadata">

**Author:** ![gcampbell](https://avatars.discourse-cdn.com/v4/letter/g/3ab097/32.png) [@gcampbell](https://forum.inductiveautomation.com/u/gcampbell)\
**Post date:** [October 9, 2017, 2:18pm UTC](https://forum.inductiveautomation.com/t/automatic-report-query-read-timeout/15593/12 "2017-10-09T14:18:21Z")

</div>

I will try that and let you know. Thanks

---

<div class="post-metadata">

**Author:** ![gcampbell](https://avatars.discourse-cdn.com/v4/letter/g/3ab097/32.png) [@gcampbell](https://forum.inductiveautomation.com/u/gcampbell)\
**Post date:** [October 9, 2017, 3:39pm UTC](https://forum.inductiveautomation.com/t/automatic-report-query-read-timeout/15593/13 "2017-10-09T15:39:33Z")

</div>

> [@gcampbell](#):
>
> SELECT CEMS\_DATA\_MIN.DATE\_TIME,CEMS\_DATA\_MIN.UNIT\_ID,CEMS\_DATA\_MIN.DATA32,CEMS\_DATA\_MIN.DATA30FROM CEMS\_DATA\_MINWHERE CEMS\_DATA\_MIN.UNIT\_ID = 2154ORDER BY CEMS\_DATA\_MIN.DATE\_TIME

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 ☹ . 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

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [October 9, 2017, 4:38pm UTC](https://forum.inductiveautomation.com/t/automatic-report-query-read-timeout/15593/14 "2017-10-09T16:38:18Z")

</div>

> [@gcampbell](#):
>
> SELECT CEMS\_DATA\_MIN.DATE\_TIME,CEMS\_DATA\_MIN.UNIT\_ID,CEMS\_DATA\_MIN.DATA32,CEMS\_DATA\_MIN.DATA30FROM CEMS\_DATA\_MINWHERE CEMS\_DATA\_MIN.UNIT\_ID = 2154ORDER BY CEMS\_DATA\_MIN.DATE\_TIME

Try the following in the Query Browser:

```auto
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:

```auto
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](https://www.w3schools.com/sql/sql_top.asp)

---

<div class="post-metadata">

**Author:** ![gcampbell](https://avatars.discourse-cdn.com/v4/letter/g/3ab097/32.png) [@gcampbell](https://forum.inductiveautomation.com/u/gcampbell)\
**Post date:** [October 9, 2017, 6:18pm UTC](https://forum.inductiveautomation.com/t/automatic-report-query-read-timeout/15593/15 "2017-10-09T18:18:51Z")

</div>

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