With (Recursive) Query - Teradata Warehouse Miner

Teradata® Warehouse Miner™ User Guide - Volume 2ADS Generation

Product
Teradata Warehouse Miner
Release Number
5.4.6
Published
November 2018
Language
English (United States)
Last Update
2018-12-07
dita:mapPath
gxn1538171534877.ditamap
dita:ditavalPath
ft:empty
dita:id
B035-2301
Product Category
Software
The Recursive component of a With (Recursive) Query is not available on an Aster system.
This element can not be used recursively on an Aster system.
A With (Recursive) Query element may only be placed immediately underneath a With (Recursive) Clause or a With (Recursive) Group. This element may be used to create either a With Query, a With Recursive Query or a With Recursive View (when not part of a group). A With Query consists of one or more seed queries and a base query, whereas a With Recursive Query consists of one or more seed queries, one or more recursive queries and a base query. Descriptions are listed below. Examples are provided in the subsequent sections.
A With Recursive View does not contain any defined variables in its base query.
  • Base query — The base query is defined in the analysis that contains the With Recursive Clause.

    A separate analysis must be created for each seed query (there can be more than one), and if applicable, for each recursive query (again, there can be more than one). A column, any column, created by each of these analyses should be dragged onto the folder labeled With Query Analyses. To do this, the Input Source must first be set to Analysis, and the analysis selected underneath. This links the base analysis to the seed and recursive analyses.

    • With Recursive View — Note that in order to specify a With Recursive View rather than a With Recursive Query, it is necessary first to specify on the Output - storage tab for the base analysis that a view is to be created. It is then also necessary to remove all variable definitions from the base analysis in order to create a With Recursive View.
  • Seed query — A seed query is created using a Variable Creation analysis that has the With seed query option checked on the analysis parameters tab. Further, the option to Store the tabular output of this analysis in the database must not be checked. In order to actually see the seed query in the final base query SQL (rather than a selection from a volatile table), the option to Generate the SQL for this analysis but do not execute it should be checked on the Output - storage tab.
    The name of the first seed query analysis is used for the name of the With Query or With Recursive Query. Each seed query should select the necessary columns from its table of interest, and optionally a constant 0 to represent recursive level or depth (if it is desired to limit the depth of recursion). Also, each seed and recursive query should produce the same result set columns, as all the queries are joined together with UNION ALL operators.
  • Recursive query — A recursive query is created using a Variable Creation analysis that has the With recursive query option checked on the analysis parameters tab. Further, the option to Store the tabular output of this analysis in the database must not be checked, and the option to Generate the SQL for this analysis but do not execute it must be checked on the Output - storage tab.

    Each recursive query joins a table of interest with the With Query itself, that is a pseudo-table with the same name as the first seed query analysis. If the Where clause of the recursive query contains all the necessary join conditions to do this, it will simplify the SQL to set the join path type for joining the table of interest with the With Query to Omit on the anchor table tab. The join conditions in the Where clause may also contain a condition to limit the level or depth of recursion (for example, level ≤ 3).

    There are no special properties for the With (Recursive) Query element.