# Error Report Viewer, using nested Query **URL:** \ **Category:** Ignition\ **Tags:** vision\ **Created:** [December 13, 2023, 3:19pm UTC](https://forum.inductiveautomation.com/t/error-report-viewer-using-nested-query/83090 "2023-12-13T15:19:15Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![sebastien\_ng](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/sebastien_ng/32/55297_2.png) [@sebastien\_ng](https://forum.inductiveautomation.com/u/sebastien_ng)\ **Post date:** [December 13, 2023, 3:19pm UTC](https://forum.inductiveautomation.com/t/error-report-viewer-using-nested-query/83090/1 "2023-12-13T15:19:15Z")
I have 3 tables I'am using nersted Query. ```auto SELECT turbine.id_turbine AS ID_TURBINE, turbine.localisation, turbine.etat, turbine.marque, turbine.vitesse FROM turbine ``` ```auto SELECT control.date_control, equipe_technique.code_acces, equipe_technique.role, equipe_technique.priorite, control.date_next_control, control.details_control FROM control, equipe_technique WHERE equipe_technique.id_responsable = control.id_responsable AND control.date_control BETWEEN ? AND ? AND control.id_turbine = ? ``` Sometimes it work ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/b/3/b30327a1b13def5814c67d59f32b8cfd3b090f36.png) Sometimes, when I change start and end date, it is causing probleme. ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/b/d/bd2643967d5cfc06f938f81f79e0f2e26586d007.png) ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/c/b/cbc0369e0ac53dfa8e4697de062c1bad33a0e3b8.png) ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/8/7/872663e5da11ddecb93db881f65e2ddef49e9efb.png) error: ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/7/8/788e6afd14c8624b9b6fc32386a89a7faed70149.png) ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/e/2/e213b9194b4fdef1157733dfcd2e2a5e809a7f91.png) sometimes I lose the gateway connection. ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/f/9/f9573fcd90cf75f2190051bc0e8c43c5c7cbea7c.png) I don't know if is nersted query that is not working good or my SQL query is not good . ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [December 13, 2023, 3:30pm UTC](https://forum.inductiveautomation.com/t/error-report-viewer-using-nested-query/83090/2 "2023-12-13T15:30:06Z")
> [@sebastien\_ng](#): > > sometimes I lose the gateway connection. That often means your query (and following report generation) is taking more than 60 seconds. Or having a pathological load on the gateway. Monitor your gateway's Performance page while trying to run longer timespans. Test your query by itself in the designer's DB tool. ---
**Author:** ![bschroeder](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bschroeder/32/13260_2.png) [@bschroeder](https://forum.inductiveautomation.com/u/bschroeder)\ **Post date:** [December 13, 2023, 3:33pm UTC](https://forum.inductiveautomation.com/t/error-report-viewer-using-nested-query/83090/3 "2023-12-13T15:33:11Z")
You may want to look at > [@sebastien\_ng](#): > > ```auto > SELECT control.date_control, > equipe_technique.code_acces, > equipe_technique.role, > equipe_technique.priorite, > control.date_next_control, > control.details_control > FROM control, > equipe_technique > WHERE equipe_technique.id_responsable = control.id_responsable AND > control.date_control BETWEEN ? AND ? AND control.id_turbine = ? > > ``` Use joins between your equipe\_technique and control tables rather than comparison in the where statement if possible. Also, ensure you have a proper foreign key between those two tables and that your tables are properly indexed. ---
**Author:** ![sebastien\_ng](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/sebastien_ng/32/55297_2.png) [@sebastien\_ng](https://forum.inductiveautomation.com/u/sebastien_ng)\ **Post date:** [December 13, 2023, 3:39pm UTC](https://forum.inductiveautomation.com/t/error-report-viewer-using-nested-query/83090/4 "2023-12-13T15:39:19Z")
> [@pturmel](#): > > Test your query by itself in the designer's DB tool. I test but it not take many secondes ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/b/1/b1e59ed14d60efba2b908c2068778c611a5c0418.png). What it mean ? ---
**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\ **Post date:** [December 13, 2023, 3:45pm UTC](https://forum.inductiveautomation.com/t/error-report-viewer-using-nested-query/83090/5 "2023-12-13T15:45:27Z")
Thing is, that's just the 2nd part of your nested query. It will be repeated for every record in your turbine table. I seem to remember Phil providing a way to build the data structure required by table groups in a script. Maybe you could get better results running only one query and then building the structure with Phil's method ? Though make sure it's actually a timeout problem before jumping down that hole. ---
**Author:** ![sebastien\_ng](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/sebastien_ng/32/55297_2.png) [@sebastien\_ng](https://forum.inductiveautomation.com/u/sebastien_ng)\ **Post date:** [December 13, 2023, 4:28pm UTC](https://forum.inductiveautomation.com/t/error-report-viewer-using-nested-query/83090/7 "2023-12-13T16:28:43Z")
> [@pascal.fragnoud](#): > > Maybe you could get better results running only one query and then building the structure with Phil's method ? `En vrai, je n'ai pas trop compris ce que Philp demande pour que je teste.` D'apres le doc, nersted query est plus effciace pour creer les rapport avec des tableau de données liée. Si je crée une seule requette, comment je vais creer le tableau ci dessus dans le rapport ? ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/7/6/76b5f6d7a5586c6dc1fcf19c313c6034c847705c.png) ce que j'attend sur mon rapport est le meme que l'exemple donnée sur le doc: [Table Groups - Ignition User Manual 7.9 - Ignition Documentation](https://docs.inductiveautomation.com/display/DOC79/Table+Groups) ---
**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\ **Post date:** [December 13, 2023, 4:32pm UTC](https://forum.inductiveautomation.com/t/error-report-viewer-using-nested-query/83090/8 "2023-12-13T16:32:44Z")
avec ca: > [@Report with Scripted Nested Data](https://forum.inductiveautomation.com/t/report-with-scripted-nested-data/11801): > > The report datasource scripting examples explain how to access the nested data in another key for use constructing a custom dataset. What I don’t see is how to construct nested data that would be suitable for use with a child table in a table group. I have a parent dataset that I’m constructing via script due to annoying limitations of an ancient SQL Server 2000 instance. I need a subquery for each row that could be done with a normal query, but there’s no way to add a regular subquery to a s… et un `itertools.groupby`. Mais te lances pas là dedans tout de suite, attends un peu d'avoir d'autres avis. ---
**Author:** ![sebastien\_ng](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/sebastien_ng/32/55297_2.png) [@sebastien\_ng](https://forum.inductiveautomation.com/u/sebastien_ng)\ **Post date:** [December 13, 2023, 4:38pm UTC](https://forum.inductiveautomation.com/t/error-report-viewer-using-nested-query/83090/9 "2023-12-13T16:38:33Z")
merci Pascal, Oui, vais attendre d'autres les avis 😀