Subquery - Teradata Warehouse Miner

Teradata Warehouse Miner User Guide - Volume 2ADS Generation

Product
Teradata Warehouse Miner
Release Number
5.4.5
Published
February 2018
Language
English (United States)
Last Update
2018-05-03
dita:mapPath
qhj1503087326201.ditamap
dita:ditavalPath
ft:empty
dita:id
B035-2301
Product Category
Software
A Subquery element may be used in many contexts in an expert clause, including the following:
  • after IN / NOT IN
  • after EXISTS / NOT EXISTS
  • after comparison operator
  • after LIKE predicate
  • after ANY / ALL (in one of the above)
  • before/after OVERLAPS (only available on a Teradata system)

In the following example, a Subquery element is used within an IN predicate in a Where Clause. Note that underneath it is the name (Variable Creation Subquery4) 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 selecting the desired analysis and possibly table. Note that a Variable Creation analysis that represents a subquery must have the Subquery option checked on the analysis parameters tab.

Variable Creation > Input > expert options > Where Clause > 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. Note that 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 Where Clause 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. Consider the following points.
  • If this is to be a correlated subquery, the Generate the SQL for this analysis but do not execute it option must be set.
  • 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.
  • 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).

An example of the SQL generated by a query with a subquery is as follows.

SELECT
	 "_twmVC0"."cust_id" AS "cust_id"
	,"_twmVC0"."income" AS "income"
	,"_twmVC0"."age" AS "age"
FROM "twm_source"."twm_customer" AS "_twmVC0"
WHERE "_twmVC0"."cust_id" IN (
SELECT DISTINCT
	 "_twmVC0"."cust_id" AS "cust_id"
FROM "twm_source"."twm_credit_acct" AS "_twmVC0"
WHERE "_twmVC0"."account_active" = 'Y')
ORDER BY "_twmVC0"."cust_id"
A subquery built with a Variable Creation analysis may be made a correlated subquery by making the subquery analysis specifically refer to an input table in the containing analysis through the use of a unique table alias prefix. Thus, the containing analysis (i.e., the one using the Subquery element) should specify a Table Alias Prefix on the analysis parameters tab, and an expert clause condition in the subquery analysis should refer to a specific table reference. This can be done either by setting the Correlation Name on the Properties dialog for an input column, or by using a SQL Text element or a Text literal.
The first approach can be used only if the subquery selects from the same table it needs to reference in the outer query. Otherwise, a SQL Text or Text Literal must be used rather than a column element.

An example of the SQL generated by a query with a correlated subquery is as follows, with the Correlation Name, T0, highlighted in the second Where Clause.

SELECT
	 "T0"."cust_id" AS "cust_id"
	,"T0"."age" AS "age"
	,"T0"."married" AS "married"
FROM "twm_source"."twm_customer_analysis" AS "T0"
WHERE "T0"."age" >
(SELECT
	 AVG("A0"."age") AS "avg_age"
FROM "twm_source"."twm_customer_analysis" AS "A0"
WHERE "A0"."married" = "T0"."married")
ORDER BY "T0"."married", "T0"."age"

There are no special properties for the Subquery element.

For more information, refer to Complex Queries.