In order to accommodate table operators, either system supplied or user supplied, that allow more than one ON clause (input source), an advanced form of the Table Operator SQL element is provided. When a Table Operator (advanced) is dragged onto the Tables work area or otherwise selected, a Function Table that looks like the following results displayed in the figure later in this section (Variable Creation > Input > Variables: SQL Elements pane - Table Operators > Table Operator (advanced)).
Notice that an initial ON Clause SQL element has been placed in the ON Clauses folder. Additional ON Clauses may be specified by dropping one or more additional ON Clause elements onto the ON Clauses folder.
Besides supporting multiple ON Clauses, the advanced SQL Element for Table Operators provides examples and usage information in a manner similar to that provided for Aster SQL Map Reduce functions. The Arguments tab is equivalent to the Name/Value Pairs tab on the dialog for the basic Table Operator. The information that is specified on the Order/Hash/Partition tab of the basic form is instead specified on the dialog for each ON Clause that is utilized with the advanced form. Finally, the SQL elements for most of the system-supplied Table Operators lead to the display of the basic dialog. Any of the system-supplied Table Operators may, however, be displayed using the advanced dialog by first selecting the generic advanced dialog and then selecting the desired function in the dialog.
Table Operator (Advanced) utilizes substitution parameters in a manner similar to a SQL Text with Arguments SQL element, replacing <P1> with the SQL generated for the first expression in the Substitution Parameters folder, <P2> for the second, and so forth. By default, substitution parameters are not included with a table operator. You must check the Include Substitution Parameters checkbox in the Table Operator (advanced) Properties dialog in order to use substitution parameters with the function.
The Advanced Table Operator:
- Table, via a Column in the table — Dropping a column onto the (empty) node results in the table that contains that column being used as the input table to the operator.
- Table, via a Table Function — Dropping a Table function onto the (empty) node results in the table created by that Table function being used as the input table to the operator.
- Table, via a Table Operator — Dropping a Table Operator onto the (empty) node results in the table created by that Table Operator being used as the input table to the operator (thus allowing nesting of Table Operators).
- Query, via a column in an Analysis Table — When any column from an Analysis Table is dropped onto the (empty) node, the SQL from that analysis becomes the query input to the table operator, provided that Gen SQL Only is checked for the referenced analysis.
- Query from a SQL Text or SQL Text with Arguments element — A SQL Text or SQL Text with Arguments element can be used to specify a query that represents the input table to the Table Operator.
To view or set the properties of the operator, double-click on Table Operator, or highlight it and click Properties.
Database Name — Name of the database where the Table Operator is installed. The desired value may either be typed into this field or selected from the pull-down list.The pull-down list for this field contains all of the Source Databases defined on the Connection Properties dialog (accessed from the Tools menu of the main application). Values may be added or removed there.
- Operator Name — Name of the Table Operator to be invoked. The desired value may either be typed into this field or selected from the pull-down list. The pull-down list is populated by issuing the appropriate Teradata Help command or by querying the data dictionary and by filtering out all objects except Table Operators.
- Description — Description of the Table Operator in a read-only field.
Arguments — Argument Name/Value pairs for the operator should be entered on this tab. Note that the values are all enclosed in parentheses.
COLUMN_LIST ('month_1_avg_tran_amt', ...'month_12_avg_tran_amt')
COLUMN_ALIAS_LIST ('01-jan', ...'12-dec')These are argument pairs for the TD_UNPIVOT Table Operator, with “…” representing additional values; they are not part of the syntax.
- Usage — This tab is not currently used on this dialog.
- Examples — Examples as SQL for this function, as available. The information shown here may apply to this dialog and to the ON Clause dialog, as described below.
- Omit Database In Call — When checked, this option causes the operator name to appear without the containing database name preceding it in the SQL generated for this Table Operator. In this case, upon invocation of the Table Operator in generated SQL, the database software will first search for the Table Operator in the current database (either the default database of the data source connection or the last issued DATABASE command) and, if necessary, in standard system databases (for example, SYSLIB, TD_SYSFNLIB and SYSUDTLIB).
- Include Substitution Parameters — When checked, usage of substitution parameters in the argument values is allowed in a manner similar to a SQL Text with Arguments SQL element, replacing <P1> with the SQL generated for the first expression in the Substitution Parameters folder, <P2> for the second, and so forth. The Substitution Parameters folder will be added to the Function Table tree once the OK or Apply button is clicked in the Properties dialog.
Detailed information about the specific Table Operators below can be found in the SQL Functions, Operators, Expressions, and Predicates document, B035-1145, for the appropriate release. Due to the complexity of these operators, it is recommended to review this document.
The empty node under ON Clause in the following figure is equivalent to the node underneath the Table Operator (basic) node as described in Table Operator (basic) (Teradata Database).
To view or set the properties of the operator, double-click on ON Clause (Partition By), or highlight it and click Properties.
- Partition By List — This is an alternative to the Hash By List, requesting partitioning instead. Note that it is not used by the CALCMATRIX operator.
- Partition By Any — This option can be specified by entering the word ANY in the Partition By List. PARTITION BY ANY specifies a table with no partitioning or order by attributes. PARTITION BY ANY preserves the existing distribution of the rows on the AMPs. A PARTITION BY ANY clause followed by an ORDER BY clause means that all the rows are ordered by the ORDER BY clause on that partition, and it is functionally equivalent to using LOCAL ORDER BY without using a HASH BY clause. You cannot specify a PARTITION BY ANY and a LOCAL ORDER BY clause in the same ON clause.
- Dimension — Specifies that a duplicate copy of the dimension table is created for every partition on which the function operates. Dimension input is useful when the input table is a small look up table for facts. Each AMP holds one instance of the look up table in memory and uses it to process each row of another input.
- Hash By List — Before rows are delivered as input to the Table Operator, they may optionally be distributed across the Teradata AMPS using the Teradata hashing scheme based on the columns in this list.
- Other — When selected, the text box below can be used to enter any desired clause.
- Order By List — This option determines how result sets are sorted. In the absence of this clause, result rows are returned unsorted. You cannot specify ORDER BY as the only option in an ON clause. You must combine it with a PARTITION BY, PARTITION BY ANY, HASH BY or DIMENSION clause. If you specify an ORDER BY clause along with a PARTITION BY clause, all the clauses must have the same number of ORDER BY columns, and the data types of the columns must be the same type or matched using CAST.
- Local Order By List — Rows that are to be delivered as input to the Table Operator may optionally be ordered locally on each AMP by the columns in this list (after any requested hash distribution or partitioning is performed). Columns may be listed by name, position or expression resolving to a column, followed by an optional ascending (ASC) or descending (DESC) designator, followed optionally by NULLS FIRST or NULLS LAST.
- ON Clause Alias — Alias associated with a particular ON Clause.
When it is desired to include a RETURNS clause in the invocation of a Table Operator, the Other option may be selected in the On Clause dialog and the RETURNS clause entered in the Attributes field. (An example of a RETURNS clause is RETURNS (col1 JSON CHARACTER SET UNICODE), naming the column to be returned col1, setting the type to JSON, and setting the character set to UNICODE.) If there is more than one On Clause at the same level (not nested) in the Table Operator invocation, the RETURNS clause should appear in the last one, placing the RETURNS clause between the ON Clause(s) and the USING clause.
For more information about these clauses, refer to the SQL Data Manipulation Language document, B035-1146, in the section describing the FROM clause.