This SQL element, when selected, leads to a dialog similar to that given for a Table Function. It may be used to specify a Table Operator that was either user-defined or system-supplied. For convenience however, the system-supplied Table Operators are listed in the SQL Elements tree in various categories just below this element. When these are selected however, the advanced Table Operator dialog is displayed rather than the basic Table Operator dialog. (EXECR is an exception to this however, displaying the basic Table Operator instead.)
To execute a Table Operator, it must first have been compiled and installed in a Teradata database.
When dragging a Table Operator element into a Function Table, the following tree element is created.
Variable Creation > Input > Variables: SQL Elements pane - Table Operators > Table Operator (basic)
The “(empty)” node underneath the Table Operator node is used to specify the input table or query for most operators, deriving it from one of the following entities.
- 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.
Variable Creation > Input > Variables: SQL Elements pane - Table Operators > Name/Value Pairs Properties
The following is a description of the properties of a Table Operator that may be set using the Properties dialog.
-
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.
-
Name/Value Pairs — Parameter Name/Value pairs for the operator should be entered on this tab. Note that the values are all enclosed in parentheses. For example:
VALUE_COLUMNS ('monthly_sales')
UNPIVOT_COLUMN ('month')
COLUMN_LIST ('month_1_avg_tran_amt', ...'month_12_avg_tran_amt')
COLUMN_ALIAS_LIST ('01-jan', ...'12-dec')
These are Name/Value pairs for the TD_UNPIVOT Table Operator, with “…” representing additional values; they are not part of the syntax. -
Order/Hash/Partition — A HASH BY, PARTITION BY, PARTITION BY ANY, LOCAL ORDER BY or ORDER BY clause may be specified on this tab. Note that the clauses HASH BY, PARTITION BY and LOCAL ORDER BY only apply to a Table Operator if the input table is a derived table, With query or view (not a table). Note also that not all Table Operators use these options (for instance, TD_UNPIVOT does not). This tab and its sub-fields are displayed below.Variable Creation > Input > Variables: SQL Elements pane - Table Operators > Order/Hash/Partition Properties
- 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.
- 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.
- 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.
- 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.
-
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).
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, Teradata recommends that you review this document.