16.20 - name (value) - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Syntax and Examples

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Published
March 2019
Language
English (United States)
Last Update
2019-05-24
dita:mapPath
wkf1512081455740.ditamap
dita:ditavalPath
TD_DBS_16_20_Update1.ditaval

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
  • Scalar subquery expression (SSQ)
  • System variables
  • User variables
  • Concatenated variable expression
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
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)
Variable
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.