Using UDFs in SQL statements

 

Scalar and column user-defined functions (UDFs) can be called within an SQL statement almost everywhere an expression is valid. Table UDFs can be called in the FROM clause of a SELECT statement. Listed here are a few restrictions on the use of UDFs.

• UDFs and system generated functions cannot be specified in check constraints. Check constraints also cannot contain references to some built-in functions that are implemented by the system as UDFs.

• External UDFs, SQL UDFS and the built-in functions DLVALUE, DLURLPATH, DLURLPATHONLY, DLURLSCHEME, DLURLCOMPLETE, and DLURLSERVER cannot be referenced in an ORDER BY or GROUP BY clause, unless the SQL statement is read-only and allows temporary processing (ALWCPYDTA(*YES) or (*OPTIMIZE)).

• Using parameter markers or the NULL values as function arguments
  You cannot use a parameter marker or a NULL value as a function argument. Because function resolution does not know what data type this type of argument might turn out to be, it cannot resolve the reference.

• Using qualified function references
  If you use a qualified function reference, you restrict the search for a matching function to the specified schema.

• Using unqualified function references
  You can use an unqualified function reference instead of a qualified function reference. When searching for a matching function, DB2^® normally uses the function path to qualify the reference.

• Summary of function references
  For both qualified and unqualified function references, the function selection algorithm looks at all the applicable functions, both built-in and user-defined functions, that have the given name, the same number of defined parameters as arguments, and each parameter identical to or promotable from the type of the corresponding argument.

 

Parent topic:

Using user-defined functions