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