This lesson is part of the Reporting in Ignition course. You can browse the rest of the lessons below.

LESSON LIST

Autoplay Off

Supplemental Videos

Description

Nested Queries are a new feature in Ignition 7.8, and allow for the creation of child queries that are called once for each row returned by the parent.

Video recorded using: Ignition 7.9

Transcript

(open in window)

[00:00] Nested queries are a new feature in Ignition 7.8. The idea behind a nested query, is that you can have a parent query running and several children queries that are running for each row returned by the parent. We'll begin in the data panel, I have a data source named parent. And it's selecting some equipment details from my equipment details table, including an equipment ID number. We can create a nest query, by heading to the lower right hand corner, under where it says nested queries and clicking on the plus icon. We'll click on the data key property, and rename this to child. And I have a query ready to go, let me paste that in. Now one of the benefits of using a nested query, is that the child query can reference columns returned by the parent query. And where we have this parameter under query, and this parameter's one fields here because there's question mark in our query above. And we can ahead and use a reference to equipment ID here. So we'll head back to our child query and under parameter one, we'll put equipment ID. Note that since the parent query is using an aliased column name, I'm referencing the aliased name. If my parent wasn't using an aliased column, then I'd just ID instead. Now it's always a good idea to give your columns aliases when using nested queries. The reason for this is, is if I mistyped or there was a typo in the parameter for the child query, then the child would attempt to check all of the columns returned by the parent query. If the child didn't find a match, it would check the parent of the parent's columns, and so on and so forth until it ran out of parents. Once there's no more parents to check, then the child will check your parameters. So if any of those parameters or any of those other parents' column names are similar to the column that you were originally looking for, then you'll find yourself suddenly looking at the wrong data. Additionally, if you don't use an alias and someone modifies your database tables, renames your columns, maybe swaps some names, again, you'll be looking at the wrong data. So it's always a good idea to alias your columns and try to use unique names when possible. I mentioned it briefly, but it is possible for child queries to have their own subqueries. If you click on the plus icon with the child selected, a new subquery will appear, nested below the child. Additionally, you can have a peer query. You select the child, click on the plus icon again, you'll see there's another subquery created at the same level. These are showing up as red because you need to give each nested query a unique name. So if we rename one of them, there we are. Unlike the parent-child relationship, peers are unable to reference columns from another peer. Now since we're not going to use this extra subquery, I'm going to delete it. Now that we have our query in place, let's take a look at how this appears in the design panel. And if we look in the key browser, we'll expand data sources, we see there's our parent data source, and right next to the keys in the parent, there's the child data source. And nested under it are some keys. To demonstrate what the data looks like, I've created two tables on my report. There's a parent table, with a data key of parent, and then a child table, the data key of the child. Note the path here, since child is a child query to the parent. To help keep things simple, I gave all the rows on the child table a blue background. The parent table is going to be showing an equipment name and an equipment ID column. The child table will show a downtime cause column, as well as an equipment ID column. Now the equipment ID column on the child should be a matching number with the parent equipment ID column, assuming that there is a downtime cause for that equipment ID number. Let's take a look at how this appears in the preview panel. So we have the query returned by our parent and it's simply showing each row on that table. The child table however, is doing something a bit different. Let's step through this real quick. The parent query runs and returns all these rows. Next, the child query, seeing that the parent returned some rows, starts executing. Now remember, every execution of the child query is looking for a specific equipment ID in the where clause. And it didn't find anything with equipment ID of one. So, no rows were returned by the child query in that execution. Next, the child query executes for the second row of the parent, which is looking for equipment ID two. This time, the child query returned three rows for equipment ID two. Which is what we see on our child table. The child query then executed for equipment ID three, but didn't find any rows. Equipment ID four, didn't find any rows. Equipment ID five found one row. Equipment ID six found two rows and so on. So you see that nested queries allow you to run queries that are dependent on returned values from the parent. And are commonly used in parent-child table groups.

You are editing this transcript.

Make any corrections to improve this transcript. We'll review any changes before posting them.