Designing a Subquery

Sub-queries are queries used inside a query condition expression. The result of the sub-query is used when evaluating the expression. This functionality is only supported for hierarchical data sources like XML, SOAP, etc. You cannot use this feature with a non-hierarchical data source like JDBC. This concept also exists in SQL, but there are a few important differences:

• A sub-query can be used in an expression where a scalar or list value is expected. This is different from SQL sub-queries, which can only be used in a few specific types of expressions.
• A sub-query is referenced by the query name. The definition of the sub-query is not included in the expression. In SQL, the sub-query’s definition is embedded in the expression where the sub-query is used. By referencing a sub-query by its name, the sub-query definition can be shared by more than one query, and the resulting query is easier to maintain.
• A sub-query can use a different data source from the parent query. This is very powerful in a hybrid data source environment. For example, an XML query can use a sub-query that retrieves its data from an EJB. This allows the application to ignore the underlying implementation of the data model and unify the data access interface.

Invoking a Sub-Query

A sub-query can be invoked from an expression using the ‘query()’ function. name in (query('all names')) In this example, the results returned by the query, ‘all names’, are used as the list value. The query must be defined in the query registry. If the sub-query expects parameters (user variables), the variable values can be passed to the sub-query in the function call. name in (query('all names', state = state) One or more parameters can be added to the call as needed. Each variable is passed in as a name-value pair.

In this example, the name of the variable is ‘state’, and the value of the variable is the ‘state’ node value in the current record. Any name values can be used as the variable value, including variables, sub-queries, nodes or attributes, and constant values.

“Flexible product with great training and support. The product has been very useful for quickly creating dashboards and data views. Support and training has always been available to us and quick to respond. - George R, Information Technology Specialist at Sonepar USA

Walkthrough: Designing the Subquery

Next we provide an example of using a sub-query to get a list of values from an XML data stream and then using the query result in the condition of another query to retrieve a list of employees. The query retrieves a list of all employees with evaluation scores above a score of 3.

First, create a query to retrieve the employee ID’s of employees with scores above a certain value.

1. Create a query, “Scores”, using the ‘Personnel’ data source.
2. Select the ‘default’ request type.
3. On the Fields tab, select the ‘id’ field, and click the ‘add’ button.
4. On the Condition tab, select the ‘score’ field, click the ‘add’ button, and set the condition to: score >= $(score)
5. Click ‘Finish’. Notice that the ‘Map to Table’ option is selected. It would not be nec¬essary to map the result to a table if the return value was a child node. In this case, however, you need to return the employee ID, which is an attribute in the employee record. Attributes can only be returned as a table column. This query returns a table with a single column, the employee ID. The score comparison is done against the ‘Score’ variable.