Nested Queries

Nested Queries

Watch the video

What are Nested Queries?

The simple definition is that a Nested Query uses the results of a previously executed query to collect data. The general structure of a Nested Data Source is one in which you have a Parent query and child queries. Each child query can then use the columns from the parent query as parameters using {columnName}. For example, if a parent query had a column called machineID, the child query can then use {machineID} as a parameter, and the child query will run for each row of the parent query, using the different values of machineID for each run. Those well-versed in SQL are probably thinking that this sounds like a JOIN and in fact, there are some similarities. There are also some major differences which allow Nested Data to be both easier and more powerful:

• Nesting relationships are not restricted to data in a single schema, database or even source! Nesting is easy to configure across tables, between different databases, or even with sources like the Tag Historian!
• Writing queries for nested query sources can be far simpler and easier to maintain than writing complex JOIN operations.
• Nested structures allow more control in how data is collected, allowing data structures and relationships that are more expressive.

How Nesting Works

Let's use a simple data relationship to help illustrate how nesting occurs. Imagine we have data collected from two unrelated sources that look the ones seen in the tables below.

CodePK	Code
1	ZG
2	GB
3	DC

FrequencyID	CodeID	FreqValue
1	3	11
2	1	41
3	2	13
4	3	26
5	1	13
6	3	32
7	1	11

We want to create a data source connecting all these things for reporting using nesting. The trick is to identify where the two datasources connect. You may notice in the data above The CodePK column in the Codes datasource matches up with the CodeID column of the Frequency datasource. This is where we will connect the two datasources. We will make our Codes datasource the parent and the Frequency the child. The queries would look something like below.

Pseudocode - Select Statement Examples

-- Parent Query
SELECT CodePK, Code FROM Codes
  
-- In the Child Query, accessed by clicking on the Frequency leaf of the Nested Queries Tree
-- This assumes the value of Parameter 1 would be equal to {CodePK}
SELECT FrequencyID, FreqValue FROM Frequency WHERE CodeID = ?

What this means for our resulting data is that the parent query is called first, and a set of results is returned to the parent query, named Codes. After this data has been retrieved, the Child query will execute, once for each row of the parent, substituting the value of CodePK into the child query where we have the {CodePK} reference.

The resulting data will have a structure like this:

Psuedocode - Code Frequency Structure

Codes
    |-Row 1
    |     |_ CodePK - 1
    |     |_ Code   - ZG
    |     |     Frequency
    |                   |-Row 1
    |                         |_ FrequencyID - 2
    |                         |_ FreqValue - 41
    |                   |-Row 2
    |                         |_FrequencyID - 5
    |                         |_ FreqValue - 13
    |                   |-Row 3
    |                         |_ FrequencyID - 7
    |                         |_ FreqValue - 11
    |-Row 2
    |     |_ CodePK - 2
    |     |_ Code   - GB
    |     |     Frequency
    |                   |-Row 1
    |                         |_ FrequencyID - 3
    |                         |_ FreqValue - 13
    |-Row 3
    |     |_ CodePK - 3
    |     |_ Code   - DC
    |     |     Frequency
    |                   |-Row 1
    |                         |_ FrequencyID - 1
    |                         |_ FreqValue - 11
    |                   |-Row 2
    |                         |_FrequencyID - 4
    |                         |_ FreqValue - 26
    |                   |-Row 3
    |                         |_ FrequencyID - 6
    |                         |_ FreqValue - 32

Each row returns the CodePK and Code from the parent query, but also the results of the child query, that apply to the CodeID we get from the parent PK.

Now, in our Report Design, we will have access to our datasource Frequency that linked these two sets of data together through a shared value.

Special Considerations

Nested Queries are powerful and easy to use, but users should be aware of runtime implications. Imagine the scenario above, where we have two sets of data, each with 5000+ rows. When our child query executes, each row of its query is going to require a lookup from the parent. For most common sets of data and database sizes, this won't be an issue, but it's possible to imagine that instead of just one child query, we have a dozen.

In addition, some of those children also have many children. It's very easy to see in this scenario how exponential growth occurs and our system performance may suffer. Most report designers will limit query sizing as oversized data structures are simply not as easy to work with. However, if you feel an urge to generate massive complex trees of million line queries, you may be waiting a while.

Nested Query Example - Equipment Downtime

Here in this example, we have two database tables: an equipment table, which contains all of the equipment we have, as well as a downtime table, which contains a list of every downtime event. The tables are put together like the tables below.

Equipment_Table

Id	Name	Description
25489	Conveyor Line	Transfers product
55684	Labeler	Makes labels
88456	Palletizer	Makes pallets
626145	Filler	Fills tanks

Downtime_Table

Id	Equipment_id	Cause	Minutes_Down
1	25489	Backup	22
2	55684	Out of labels	25
3	55684	Stuck labels	15
4	88456	Misalignment	38
5	626145	Overflow	50
6	25489	Scheduled Maintenance	12
7	626145	Scheduled Maintenance	40
8	88456	Misalignment	55

1. Create two tables in the database that are similar to the tables listed above.
2. Open a report, and navigate to the Data Panel.
3. Click the Plus icon and add a SQL Query to the data sources.
4. Change the Data Key property to Equipment.
5. Type the query below into the query area. Here we are pulling out three columns of our equipment table, and giving each column a more descriptive name. Pay close attention to the spelling of the aliased names (names following the AS keywords), as we will have to reference EquipmentIDNumber later in our subquery.

SQL - Equipment Table Query

SELECT id AS EquipmentIDNumber,
  Name AS EquipmentName,
  Description AS EquipmentDescription
FROM Equipment_Table

6. On the right of the Nested Query section, click the Plus button to create a sub_query.
   

7. Select the sub_query, and rename the Data Key property to rename it. In this example, we will use the name EquipDowntime.

8. Type the following query into the query area.

   SQL - Downtime Table Query

   SELECT cause AS DowntimeCause,
     minutes_down AS DowntimeMinutes
   FROM Downtime_Table
   WHERE Equipment_id = ?

   Here we pull in our downtime table, but we only need the cause and minutes_down, since we are already grabbing the Equpiment_id from the first query. The WHERE clause is where we link this query to the parent with the equipment ids.

9. In the Parameter 1 field, type the following:

   Expression Language - Referencing a Parameter

   {EquipmentIDNumber}

   Note that we are directly referencing one of the aliased column names from the Equipment query.

That is all it takes to make a Nested Query. We now have two separate tables being called and linked together by the equipment id.

To check that your queries worked, go to the Preview tab and look at the XML data that comes up. You should have a data set inside (indented) each row of Equipment data. If you'd like, you can continue with this example and either use this data in a table group, or in a nested chart.