Product How-To: Creating Sub-queries with SQL

SQL conditions allow a sub-query to be used in certain expressions. For example, a sub-query can be used in the ‘in’ expression to serve as the list value. This concept is supported in the Data Modeler conditions.

The SQL sub-query is specified as a select statement inside a SQL condition. Using the Data Modeler, you can specify conditions on query variables such that they are dependent on the results from the sub-query.

e.g: select * from orders where orders.state in (select customers.state from customers) The result of the sub-query is interpreted according to the context.

view analytics demo
View a 2-minute demonstration of InetSoft's easy, agile, and robust BI software.

If a scalar value is expected in the expression, the sub-query result (a sub-tree) is converted to a value using the following rules:

  • If the root node is a sequence node, the value of the first child is returned.
  • If the root node value is not null, the value is used as the scalar value.
  • If the root node value is null and has child nodes, the value of its first child is returned.
  • Otherwise, the value is null.

If a list is expected in the expression, the sub-query result is converted to a list using the following rules:

  • If the root node is a table node, the first column of the table is converted to a list.
  • If the root node is a sequence node, the sequence is converted to a list by converting each child of the root to its scalar value.
  • Otherwise, a single value list is created using the scalar value of the root.

A sub-query call can be used in any place in a condition expression where a value is expected.

Sub-selection

Previously we have shown how to define a selection tree path using the Data Modeler query panel. The tree path is saved as part of a query, and is used by the query to select sub-trees from the data stream. The same tree path selection mechanism can be used inside a query condition.

A tree path can be used to select nodes from the current sub-tree.

name in (filter('employee[name = "John Smith"].subordinates.name'))

The filter() function can be used where a scalar or list value is expected, such as a sub-query or a variable. The result of the filtering is converted to a scalar or list value using the same algorithm as was used for the result of a sub-query.

A tree path is constructed by concatenating the node names from the root of the current sub-tree, separated by a dot. For example, if a condition is attached to the ‘employee’ node, a tree path to select all skill nodes is:

$(skill) in filter('employee.skill')

Sub-selection is different from referencing child nodes by name or node path. If ‘employee.skill’ is used in an expression, it refers to the skill list of the current employee. Since the condition is evaluated for each employee individually, the list only contains the skills of one employee. On the other hand, a filter always works on the entire list. The ‘filter(‘employee.skill’)’ expression returns a list of all of the skills contained on the employee list.

The other difference between a selection tree path and a regular node name path is that conditions can be added to the tree path. The condition is used to filter the nodes to be included in the sub-tree. For example, if you want to retrieve all programming skills, you can match the skill with a regular expression:

$(skill) in filter('employee.skill[this match ".*Programming"]')

As with SQL sub-queries, using a sub-tree selection in a condition expression is quite expensive. This option should only be used when no other construct achieves the same purpose.

Read the top 10 reasons for selecting InetSoft as your BI partner.

More Articles About Reporting

Authority of Engaged Stakeholders - Different stakeholders have varying degrees of power or clout in their own local communities. Monitoring the involvement of key stakeholders may be quite advantageous. Organizations may use extended networks and the credibility of their stakeholders to magnify their messages and activities by recognizing and maintaining these connections...

Create a Selection List - Expand a Data Block (table) or cube dimension in the Component tree. Select one column from the Data Block, or one level from the cube dimension, and drag it onto the Viewsheet grid. To create a Selection List follow these steps: Drag a Selection List element from the Component tree onto the Viewsheet grid. This creates an empty Selection List element. Right-click on the Selection List, and select 'Properties' from the context menu. This opens the 'Selection List Properties' dialog box. Select the Data tab. In the 'Table' list, expand a Data Block or cube, and select the column or dimension that should be used to populate the Selection List. Click 'OK'. This creates a Selection List based on the data in the selected column or dimension...

Create a Visualization Selection Tree - InetSoft's dashboard visualization tree is created by a simple process that allows users to interact with data in quick and meaningful ways. To create a Selection Tree, follow the steps below: Drag a Selection Tree component from the Component tree onto the Viewsheet grid. This creates an empty Selection Tree element. You can also drag and drop a single column directly onto the empty Selection List element. Right-click the Selection Tree, and select 'Properties' from the context menu. This opens the 'Selection Tree Properties' dialog box. Select the Data tab...

Data Mashup + Visualization = the Right Tool - So far we have seen visual analysis aspects. Demographic analysis normally requires a good deal of data. Normally this data must be collected from different sources. These sources have their own formats and delivery mechanisms. In most cases, data collecting, cleansing and transformation consumes the majority of time and resources. Futhermore, this tends to be a iterative process where data work and visual work are intertwined. Therefore, the tool must be as much a visual tool as a data tool...

Defining Report Parameters - To define report parameters within Report Designer, follow the steps below: Select Edit → Parameter from the Designer menu. This opens the 'Parameter Definition' dialog box with its Parameters tab displayed. This tab displays all existing report and data-binding parameters. Click 'New', and specify the name of the new parameter in the dialog box. Then click 'OK'. Click the 'Set Selection List' button to open the 'Selection List' dialog box. (The button's text is displayed in green if the selected parameter already has a list associated.) To enter a fixed set of labels/values for the parameter, follow these steps: Click the List tab. Click 'Add' to add a new label/value pair. Enter the desired 'Label' and 'Value'. (The 'Label' text is displayed to the user in the interface element; the corresponding 'Value' is assigned to the parameter...

Satisfied Business Intelligence Customers - Read about satisfied business intelligence customers and how organizations are using business intelligence solutions. Some provide their managers performance management dashboarding. Others use data mashup technology to combine disparate data sources to provide comprehensive dashboard reporting. All of them leverage InetSoft's business intelligence technology to derive intelligence from their data...

Previous: SQL Predicates