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.
The scope of input to the LOCAL ORDER BY clause is limited to:
- Derived tables
- Views
- WITH clause objects
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.