TOP Option Syntax | SQL SELECT Statements | Teradata Vantage - TABLE Option Syntax - Advanced SQL Engine - Teradata Database

TABLE ( function_name ( [ expression [,...] ] ) )
  [ RETURNS { [ database_name.] table_name | ( column_spec [,...] ) } ]
  [ LOCAL ORDER BY local_order_by_spec [,...] | HASH BY column_name [,...] ]
  [AS] derived_table_name [ ( column_name [,...] ) ]

Syntax Elements

function_name

Name of the user-defined table function.

expression

Any valid SQL expression.

If expression references a column from a table or subquery in the FROM clause, the table or subquery must appear before the TABLE option.

[ database_name.] table_name

Name of the table and its containing database, if different from the current database, into which the rows processed by the dynamic result row table function are to be inserted.

column_spec

column_name data_type

local_order_by_spec

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

column_name

In HASH BY clause.

Hashes rows across the AMPs in preparation to be input to a table function.

HASH BY is a Teradata extension to the ANSI SQL:2011 standard.

You can only specify a HASH BY clause for a statement whose result is to be input to a table function.

HASH BY must be specified as part of a FROM clause. See FROM Clause.

The scope of input to the HASH BY clause is limited to:

• Derived tables
• Views
• WITH clause objects

You cannot specify a HASH BY clause with a derived table, view, or WITH clause object that specifies set operations.

You cannot specify more than one HASH BY clause per statement.

You can specify a HASH BY clause by itself or with a LOCAL ORDER BY clause. If you specify both, the HASH BY clause must precede the LOCAL ORDER BY clause.

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

• All columns must have the same number of partitioning attributes.
• The corresponding attributes must be the same type or must be types that are compatible so that they can be implicitly cast.

If you specify a LOCAL ORDER BY clause with HASH BY input, the following is required:

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

derived_table_name

Name of a temporary derived table that other clauses in the SELECT statement can reference.

AS is an introductory optional keyword for derived table.

column_name

After derived_table_name.

Optional list of column names that other clauses in the SELECT statement can reference.

If specified, the number of names in the list must match the number of columns in the RETURNS TABLE clause of the CREATE FUNCTION statement that installed the function_name table function on the Vantage platform. The alternate names list corresponds positionally to the corresponding column names in the RETURNS TABLE clause.

If omitted, the names are the same as the column names in the RETURNS TABLE clause of the CREATE FUNCTION statement that installed the function_name table function on the Vantage platform.

column_name data_type

In column_spec.

Name and data type of one or more columns to be assigned to the row fields returned by a dynamic result row table function.

You cannot use the BLOB AS LOCATOR or CLOB AS LOCATOR forms to specify columns that have a BLOB or CLOB data type.

For more information, see Teradata Vantage™ - SQL Data Definition Language Detailed Topics , B035-1184 .

column_name

In local_order_by_spec.

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.