Table Operator Syntax | SQL SELECT Statements | Teradata Vantage - Table Operator Syntax - Advanced SQL Engine - Teradata Database

[ database_name. | user_name. ] { table_operator_name | function_mapping_name } (
  [ on_clause [...] ]
  [ EXECUTE MAP = map_name [ COLOCATE USING colocation_name ] ]
  [ out_table_clause [...] ]
  [ USING using_spec [...] ]
) [AS] correlation_name [ ( column_name [,...] ) ]

Syntax Elements

database_name

user_name

Optionally, you can specify the containing database or user. If you do not specify the database_name or user_name, the database or user defaults to one of the following, in order of precedence:

• Current default database for the session
• SYSLIB
• TD_SYSFNLIB

table_operator_name

Name for the table operator. This name cannot be a Teradata reserved word.

function_mapping_name

Function mapping name.

For information on how to create a function mapping, see "CREATE FUNCTION MAPPING" in Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.

on_clause

ON { table_name | view_name | ( query_expression ) }
  [ AS correlation_name ]
  [ hash_or_partition_by | DIMENSION ]
  [ [ LOCAL ] ORDER BY order_by_spec [,...] ]

You can have up to 16 ON clauses. You can use multiple ON clauses with all supported languages for protected and non-protected mode table operators.

If you use multiple ON clauses, the order of the ON clauses must match the order of the stream numbers in the table operator that you use.

Cogroups are used for table operators with multiple ON clauses. For information about cogroups, see Teradata Vantage™ - SQL Operators and User-Defined Functions, B035-1210.

You cannot use scalar subqueries in table operators with multiple ON clauses or ON clauses using PARTITION BY or HASH BY.

See Function Mapping and the ON Clause.

EXECUTE MAP

Specify a contiguous or sparse map and, optionally, colocation name for table operator execution. The table operator executes only on the AMPs in the map. The database distributes input rows to the AMPs in the specified or default map before running the table operator on the specified or default map if the input rows are not already distributed per that map.

You must have been granted the specified map.

When the EXECUTE MAP clause is not specified, the execute map will be as defined by the table operator. See the EXECUTE MAP option under CREATE FUNCTION and REPLACE FUNCTION (Table Form) in User-Defined Function Statements of Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.

map_name

Name of contiguous or sparse map.

You cannot specify TD_DataDictonaryMap or TD_GlobalMap.

colocation_name

Name for colocating the function on the same AMPs with other functions, tables, join indexes, or hash indexes.

You can only specify this option for a sparse map. For a contiguous map, a colocation name is not needed for colocation and the colocation_name is set to NULL.

If you do not specify a colocation name, the name defaults to database_operator, where database is the name of the database or user followed by an underscore (_) and operator is the name of the table operator. If database exceeds 63 characters, database is truncated to 63 characters. If operator exceeds 64 characters, operator is truncated to 64 characters.

out_table_clause

OUT TABLE name ( [ database_name. | user_name. ] output_table )

You can only specify the output table clause for a function mapping. See "CREATE FUNCTION MAPPING" in Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.

using_spec

name ( { value [,...] | scalar_subquery } )

You can specify variables. During function processing, variable values are substituted for the corresponding parameters in the function mapping definition. The variables are not sent for function processing. See Function Mapping and the USING Clause.

correlation_name

An alias for the column that is referenced by column_name.

column_name

Column name.

table_name

Table that is input to the table operator.

view_name

View expression that is input to the table operator.

query_expression

Query expression that is input to the table operator.

correlation_name

In on_clause.

Alias for the table.

hash_or_partition_by

{ { HASH | PARTITION } BY
    { column_name | column_position | column_expression } [,...] |

  PARTITION BY ANY
}

order_by_spec

{ column_name | column_position | column_expression }
  [ ASC | DESC ] [ NULLS { FIRST | LAST } ]

name

In out_table_clause.

Output argument name, which must match one of the OUT TABLE names in the function mapping definition.

database_name

In out_table_clause.

Name of the database containing the output table.

user_name

In out_table_clause.

Name of the user containing the output table.

output_table

In out_table_clause.

Name of the output table.

name (value)

One or more name-value pairs.

name (scalar_subquery)

You can also specify a subquery that returns a single row. Only one subquery is allowed for each name. The subquery must return only one row and the WHERE clause of the subquery must reference a unique primary index or unique secondary index. The row can have multiple values (columns), but all values must be the same data type.

HASH_BY column

column_position

column_expression

Optional set of column names on which to hash order globally the columns input to a table function or table operator.

You cannot use scalar subqueries in table operators with multiple ON clauses or ON clauses using PARTITION BY or HASH BY.

When you use a multiple input table operator that has multiple hash by clauses, the following restrictions apply:

• All of them must have the same number of partitioning attributes.
• The corresponding attributes must be the same type or matched using an implicit cast.

If you specify a LOCAL ORDER BY clause along with a HASH BY clause, the following restrictions apply:

• All the clauses must have the same number of LOCAL ORDER BY columns.
• The data types of the columns must be the same type or matched using an implicit cast.

PARTITION BY column_name

column_position

column_expression

Partition for a table specified as input to a table operator.

You can specify a column by name or position, or use an expression that resolves to a column.

You cannot use scalar subqueries in table operators with multiple ON clauses or ON clauses using PARTITION BY or HASH BY.

You cannot specify a row-level security constraint column as a partitioning column.

If you have multiple PARTITION BY clauses, the following restrictions apply:

• All of them must have the same number of partitioning attributes.
• The corresponding attributes must be the same type or matched using an implicit cast.

When you specify an ORDER BY clause with a PARTITION BY clause, the following restrictions apply:

• All the clauses must have the same number of ORDER BY columns.
• The data types of the columns must be the same type or matched using an implicit cast.

Partition keys are important to performance. If the partition keys hash to the same AMP, then some AMPs may be overloaded compared with others. Performance may also be affected if the partition key is a VARCHAR, because VARCHARs may take longer to compare than some other data types.

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

The following restrictions apply:

• You cannot specify a PARTITION BY ANY clause and a LOCAL ORDER BY clause in the same ON clause.
• If you specify multiple ON clauses with a table operator, you can only specify one PARTITION BY ANY clause. All other clauses must be DIMENSION.

DIMENSION

Specifies that a duplicate copy of the dimension table is created for every partition on which the function operates.

You can specify zero or more DIMENSION clauses for each ON clause.

DIMENSION input is useful when the input table is a small look up table for facts or is a trained model, such as that used for machine learning. For a look up table using DIMENSION, the rows are duplicated to all the AMPs. Each AMP holds one instance of the look up table in memory and uses it to process each row of another input.

For machine learning, you can store a model in the database that predicts the outcome of a particular data set and then use the stored model as dimensional input to a function.

The following restrictions apply:

• You cannot use a LOCAL ORDER BY clause and a DIMENSION clause in the same ON clause.
• If you have only one ON clause as input to a table operator, you cannot use DIMENSION in it. You must have at least one PARTITION BY or HASH BY clause in a second ON clause to use DIMENSION.
• If you use multiple ON clauses and you specify only LOCAL ORDER BY in one of them, then all other ON clauses can only specify DIMENSION.
• If you use the SCRIPT table operator, you cannot use a DIMENSION in the ON clause.

  For information about the SCRIPT table operator, see Teradata Vantage™ - SQL Operators and User-Defined Functions, B035-1210.

LOCAL ORDER BY column_name

column_position

column_expression

Qualified rows are ordered on each AMP in preparation to be input to a table function.

You cannot specify a PARTITION BY ANY clause with a LOCAL ORDER BY clause in the same ON clause.

You cannot specify a DIMENSION clause with a LOCAL ORDER BY clause in the same ON clause.

If you use multiple ON clauses and you specify only LOCAL ORDER BY in one of them, all other ON clauses can only specify the DIMENSION option.

If you use multiple ON clauses, you cannot use a LOCAL ORDER BY clause in addition to a DIMENSION clause in the same ON clause.

If you specify multiple HASH BY clauses with LOCAL ORDER BY clauses, the following restrictions apply:

• All the clauses must have the same number of LOCAL ORDER BY columns.
• The data types of the columns must be the same type or matched using an implicit cast.

ORDER BY

Defines how result sets are sorted. If you do not use 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, the following restrictions apply:

• All the clauses must have the same number of ORDER BY columns.
• The data types of the columns must be the same type or matched using an implicit cast.

When NORMALIZE and ORDER BY are specified in a query, the rows are normalized first and the result is ordered on the normalized result.