Syntax Elements - Teradata Database

Table Function Name

TABLE

Keyword to indicate a table function.

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.

RETURNS Clause

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

RETURNS column_name- data_type

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 “CREATE FUNCTION (Table Form)” in SQL Data Definition Language.

LOCAL ORDER BY Clause

Orders qualified rows on each AMP in preparation to be input to a table function.

You can only specify a LOCAL ORDER BY clause for a query whose result is to be input to a table function.

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

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

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.

ASC

Results are to be ordered in ascending sort order.

If the sort field is a character string, the system orders it in ascending order according to the definition of the collation sequence for the current session.

The default order is ASC.

DESC

Results are to be ordered in descending sort order.

If the sort field is a character string, the system orders it in descending order according to the definition of the collation sequence for the current session.

NULLS FIRST

NULL results are to be listed first.

NULLS LAST

NULL results are to be listed last.

HASH BY Clause

HASH BY column_name

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.

AS Derived Table Name

AS 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

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