Variable Creation - INPUT - Variables - Function Tables - 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 term Function Table has been coined to represent tables defined locally (i.e., in the same analysis) as Table functions, Table Operators or derived tables, to be used in the FROM clause generated by this analysis.
In the Aster environment, substitute SQL-Map Reduce function for Table function or Operator.
Once defined on the Tables tab, the columns in these tables may be selected from the column selector, as described in the section Variable Creation - INPUT - Variables. Note that until a column in a Function Table is selected into a variable, dimension, expert clause or join condition, a Function Table has no effect on the SQL generated by the analysis.
  1. On the Variable Creation dialog, click on INPUT.
  2. Click on variables on the upper tabs.
  3. Click on Tables on the large tab in the center of the panel.
  4. When the Properties button is selected and a Function Table is highlighted, the following dialog appears.
    Variable Creation > Input > Variables: SQL Elements Pane > Function Table Properties

    • Function Table Name — A name must be specified for each Function Table, though one is initially assigned automatically, beginning with Function Table1 and incrementing the trailing number.

      Function Tables can also be named by single left-clicking on the name, which produces a box around the name, as in Windows Explorer.

    • Table Function or Operator (else derived table) — When this option is checked, the Function Table is assumed to be a Table Function or Table Operator, so that the SQL generated will begin either with the keyword TABLE or a similar keyword, or else the name of a Table Operator. When not checked, the generated SQL is assumed to begin with the keyword SELECT, defining a derived table that will be placed in the FROM clause and automatically enclosed in parentheses.
      In the Aster environment, substitute SQL-Map Reduce function for Table function or Operator.
    • Description — An optional description may be specified for each Function Table.

      When the New button is selected while the Function Tables tab is selected, the following tree element is produced.

      Variable Creation > Input > Variables: SQL Elements Pane > Function Table Tree

  5. When connected to a Teradata database, the (empty) node under the Function Table node should eventually contain one of the following:
    • Table Function — Either a Table Function (any) SQL element or a specific Table function should be dropped or selected here. These are available in the Table Functions category of SQL Element.
    • Table Operator — Either a Table Operator (basic) or Table Operator (advanced) SQL element, or a specific Table Operator may be dropped or selected here. These are available in the Table Operators category of SQL element.
    • Derived Table — A derived table may be entered directly here using a SQL Text or SQL Text with Arguments SQL element. These are available in the Other category of SQL Element.
  6. When connected to an Aster database, the (empty) node under the Function Table node should eventually contain one of the following:
    • SQL-Map Reduce Function — A SQL-Map Reduce Function SQL element may be dropped or selected here. This is available in the SQL-Map Reduce Functions category of SQL Element.
    • Derived Table — A derived table may be entered directly here using a SQL Text or SQL Text with Arguments SQL element. These are available in the Other category of SQL Element.
    The Derived Table feature is simply a convenience to save the necessity of defining one or more separate analyses with Generate SQL Only selected to embed the SQL they produce into a FROM clause.
    When Advertising Output for an Analytic Data Set (ADS), the columns from a derived table on this tab will not be seen as contributing to a variable unless they appear in an argument to a SQL Text with Arguments SQL element.