Beginning with version 13 of the Teradata RDBMS, a Subquery element can be used in many contexts other than the traditional Where, Having and Qualify clauses on the Variable Creation expert options tab. Scalar subqueries (i.e., those that return a single value) can be used in the definition of variables and dimensions, and both scalar and non-scalar subqueries can be used in join path search conditions on the anchor table tab and in Group By and Order By clauses on the expert options tab. It is also worth noting that non-scalar subqueries cannot be used in Case, Coalesce or Nullif statements.
The subquery itself must be built using a separate Variable Creation analysis.
In the following example, a Subquery element is used in an arithmetic expression in the definition of a variable defined as “
age minus average of age”. Note that underneath it is the name (Average Age Subquery) of the Variable Creation analysis that will be used to generate the subquery. It is placed there by dragging any column from the analysis into the empty node below the Subquery node. This requires first setting the
Input Source to
Analysis and then selecting the desired analysis and possibly table.
A Variable Creation analysis that represents a subquery must have the Subquery option checked on the analysis parameters tab.
Variable Creation > Input > Variables > SQL Elements Pane > Other > Subquery
The output style of the analysis referenced underneath a Subquery node must not be set to
Store the tabular output of this analysis in the database. If the output option to
Generate the SQL for this analysis but do not execute it is checked on the
Output – storage tab for this referenced analysis, then the SQL it generates is placed directly into the SQL generated by the referencing analysis (the one with the Variable definition above). If, however, this option is not set, the subquery in the referencing analysis will consist of selecting all columns from the volatile table created by the referenced analysis.
If this is to be a correlated subquery, the Generate the SQL for this analysis but do not execute it option must be set. Also, whenever this option is set in order to embed the subquery SQL, until the analysis is executed, the SQL will be displayed by the SQL button as if the option was not set, showing the selection of all data from a volatile table. Finally, when an analysis containing a subquery is refreshed or published, the SQL representing the subquery is always placed directly in the containing analysis (as if the Generate the SQL for this analysis but do not execute it option was set).
The SQL generated for this example is as follows.
SELECT
"_twmVC0"."cust_id" AS "cust_id"
,"_twmVC0"."age" -
(SELECT
AVG("_twmVC0"."age") AS "avg_age"
FROM "twm_source"."twm_customer" AS "_twmVC0") AS "age_minus_avg_age"
FROM "twm_source"."twm_customer" AS "_twmVC0"
ORDER BY "_twmVC0"."cust_id"
There are no special properties for the Subquery element.
For more information, refer to Complex Queries or Variable Creation - INPUT - Expert Options - SQL Elements, the latter particularly for information about correlated subqueries and subqueries in expert clauses.