table_list - Advanced SQL Engine - Teradata Database

See Example: Function Mapping Definition with IN TABLE, OUT TABLE, and Parameters Options.

ANY IN TABLE

You can use this clause to correspond to input tables specified in the table operator ON clause during function processing that do not have a correlation name and do not match any of the named parameters. See Example: Function Mapping with Multiple ANY IN TABLE Clauses.

You can also specify a name to substitute for non-mapped tables in a table operator ON clause. In the example below, a non-mapped name specified during function processing is replaced with ConversionEvents:

ConversionEvents(ANY) IN TABLE

See Example: Function Mapping with (ANY) IN TABLE Clause Substitution.

name (value)

Parameters and values to use as defaults during function processing. The default value is used only when a SELECT statement specifies a parameter without a value in the table operator USING clause. See Example: Function Mapping Definition that Includes a Variable with a Default Value.

For value, you can specify:

• Literal value list

  You can specify character, numeric, data, time, interval, period, and graphic literals. For information about the literals you can specify, see Teradata Vantage™ - Data Types and Literals.

• Scalar subquery expression (SSQ)

  You can specify a scalar subquery expression.

  A scalar subquery can include a system variable. For example, the parameter ValueColumn has a default value specified by a scalar subquery that includes the system variable USER:

  ValueColumn(SELECT colname FROM ssqtbl WHERE username=USER)

  See Example: Function Mapping Definition with Scalar Subquery (SSQ) Substitution and Example: Function Mapping with Scalar Subquery (SSQ) Substitution that Includes a System Variable.

• System variables

  You can specify the following system variables: USER, CURRENT_USER, ROLE, CURRENT_ROLE, DATE, CURRENT_DATE, TIME, CURRENT_TIME, CURRENT_TIMESTAMP, PROFILE, DATABASE, SESSION, TD_HOST, ACCOUNT, or ZONE.

  For example, TimeOfEntry defaults to the current time, as specified by the CURRENT_TIME system variable:

  TimeOfEntry(CURRENT_TIME)

• User variables

  A variable can be a parameter name and can contain a default value, a default value list, a scalar subquery, or another variable.

  Variables specified for parameters in the function mapping definition are resolved before the parameter is sent for function processing. The value for the variable can be derived either from a default value specified in function or specified during function processing. See Example: Function Mapping Definition with Multiple Variable Substitutions.

  You can nest variables, for example:

  MaxStep(Maxnum) ,
  Maxnum(Maxnumber),
  Maxnumber(10),

  However, the resolution of nested variables cannot result in a circular reference.

  In the example below, the variable MaxStep has a default value of 150:

  MaxStep (150)

  In this example, the variable MaxStep corresponds to the variable maxnum:

  MaxStep(maxnum)

  A variable with a default value list cannot include a concatenated variable expression. For example, you cannot specify expressions similar to either of the following:

  ValueColumn(AttributeValueColumn||'_'||Maxnum)
  Maxnum(10,20)
  

  ValueColumn( AttributeValueColumn, ValCol )
  ValCol(ValColumn||'_'||MaxNum)
  

• Concatenated variable expression
  A list of string constants or variables separated by the concatenation operator ( | | ). For example, the ValueColumn parameter below has a default value specified by a concatenated variable expression:

  ValueColumn(AttributeValueColumn||'_'||Maxnum)

  See Example: Function Mapping with Concatenated Variable Substitution.

  A concatenated variable expression cannot contain a scalar subquery.

  You cannot specify a concatenated variable expression in IN TABLE and OUT TABLE clauses.

name IN TABLE

Correlation name of an input table to use during function processing. See Example: Function Mapping Definition with IN TABLE Options.

You can also specify a substitute name to enable another name to be used during function processing. In the example below, the table ConversionEvents is used instead of the table conversion for function processing:

ConversionEvents(conversion) IN TABLE

See Example: Function Mapping Definition with IN TABLE Clause Substitution.

You cannot specify IN TABLE clause variables as parameters or values in other function parameters.

You cannot specify the same variable name in multiple IN TABLE clauses.

You cannot specify ANY as a value if you use the ANY IN TABLE clause.

An IN TABLE clause cannot contain any of the following:

• Concatenated variable expression
• Scalar subquery expression (SSQ)
• Default value
• Nested values

name OUT TABLE

Name of the output table to use during function processing. You can also specify a substitute name to enable another name to be used during function processing. See Example: Function Mapping Definition with a Substitution in the OUT TABLE Clause.

You can nest OUT TABLE parameters, for example:

ModelTable(model_table) OUT TABLE ,
model_table(md_tbl) OUT TABLE 

You cannot specify OUT TABLE clause variables as parameters or values in other function parameters.

You cannot specify the same variable name in multiple OUT TABLE clauses.

An OUT TABLE clause cannot contain any of the following:

• Concatenated variable expression
• Scalar subquery expression (SSQ)
• Default value