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

Business Analytics Software Example
demo
Read how InetSoft saves money and resources with deployment flexibility.

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.

Previous: SQL Predicates