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.