The function calling argument is any simple SQL expression, including, but not limited to, constant values, column references, host variables, the NEW VARIANT_TYPE UDT constructor expression (see Teradata Vantage™ - SQL Operators and User-Defined Functions, B035-1210), or an expression containing any of these, including expressions containing UDFs.
When you call an SQL function, and that function is not stored in either your default database or in database SYSLIB, you must fully qualify the function call with a database name. If your default database and SYSLIB both contain functions matching the name of the called function, then the system references the UDF in your default database. This is the only exception that requires an explicit qualification for SYSLIB.
The argument types passed in the call must be compatible with the parameter declarations in the function definition of an existing function. If there are several functions that have the same name, and no qualifying database is specified, then the particular function that is picked is determined by the following process:
- Vantage searches the list of built-in functions.
If the called function has the same name as a built-in function, the search stops and that function is used.
If a candidate function is not found, proceed to stage 2.
- Vantage searches the list of SQL and external function names in the default user database.
Candidate functions are those having the same name and number of parameters as specified by the function call as well as the best fit based on their parameter type.
If a candidate function is not found, proceed to stage 3.
- Vantage searches the list of function names in the SYSLIB database.
Candidate functions are those having the same name and number of parameters as specified by the function call as well as the best fit based on their parameter type.
If a candidate function cannot be located, the system returns an error to the requestor.
- An SQL expression passed as an argument must not be a Boolean value expression, that is, a conditional expression.
This is also true for external UDFs.
- Nondeterministic SQL expressions, meaning expressions that use either random functions or nondeterministic UDFs, or both, that are passed as an argument must not correspond to a parameter that is used more than once in the RETURN clause of an SQL UDF.
- An SQL expression passed as an argument cannot be a scalar subquery.
The rules for selecting a best fit candidate user-defined function once its containing database has been determined are described in Function Name Overloading.