This SQL element, when selected, leads to a dialog similar to that given for a User-Defined Function. It may be used to specify a Table Function that was either user-defined or system-supplied. For convenience, however, the system-supplied Table Functions are listed in the SQL Elements tree just below this element. When selected, they lead to the same dialog as that of this element, with the indicated Table function preselected, if available.
When dragging a Table Function element into a Function Table, the following tree element is created.
Double-click on Table Function, or highlight it and click Properties.
Database Name — The name of the database where the Table Function 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.
- Function Name — The name of the Table Function 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 functions.
- Description — If Teradata Help information is available for the requested Table Function, a description of the arguments and result columns of the function are given in this field. In addition, if a Teradata Comment is available for the function, it is displayed here.
Return Columns — Generally, when return columns are not listed on the Description tab (i.e., when the return columns are not fixed), they must be specified by the user on the Return Columns tab. This tab and its sub-fields are displayed below.Variable Creation > Input > Variables: SQL Elements pane - Table Functions > Return Columns Properties
Return Columns List with Data Types — This is a comma-separated list of column/type pairs, for example:
col1 INT, col2 FLOAT, …
- Table to Get Columns From — As an alternative, if a table exists that contains exactly the columns of appropriate data type to be returned, the table name may be entered here (qualified by database if necessary, as in database.table).
- Return Columns List with Data Types — This is a comma-separated list of column/type pairs, for example:
Order/Hash By — A LOCAL ORDER BY and/or HASH BY clause may be specified on this tab. Note that these clauses only apply to a Table Function if the input table is a derived table, WITH query or view (not a table). This tab and its sub-fields are displayed below.Variable Creation > Input > Variables: SQL Elements pane - Table Functions > Order/Hash By Properties
- Local Order By List — Rows that are to be delivered as input to the Table Function may optionally be ordered locally on each AMP by the columns in this list (after any requested hash distribution is performed).
- Hash By List — Before rows are delivered as input to the Table Function, they may optionally be distributed across the Teradata AMPS using the Teradata hashing scheme based on the columns in this list.
Qualifiers — Any additional optional qualifiers that follow the invocation of a Table Function may be specified as free-form input on this tab. An example of such a qualifier is the optional WITH ORDINALITY clause that may follow the invocation of the UNNEST Table Function.Variable Creation > Input > Variables: SQL Elements pane - Table Functions > Qualifiers Properties
Number of Arguments — The number of arguments defined for the function is displayed here if Teradata Help information is available. Otherwise, this information should be provided by the user. If the Arguments folder contains fewer arguments than is specified here, the unspecified arguments will be set to NULL.
- Omit Database In Call — When checked, this option causes the function name to appear without the containing database name preceding it in the SQL generated for this function. In this case, upon invocation of the function in generated SQL, the database software will first search for the function 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 Functions in the following sections can be found in the SQL Functions, Operators, Expressions, and Predicates document, B035-1145, for the appropriate release.