DB2 Version 10.1 for Linux, UNIX, and Windows

Scalar functions

A scalar function optionally accepts arguments and returns a single scalar value each time the function is called.

A scalar function can be used wherever an expression can be used. However, the restrictions that apply to the use of expressions and aggregate functions also apply when an expression or aggregate function is used within a scalar function. For example, the argument of a scalar function can be an aggregate function only if an aggregate function is allowed in the context in which the scalar function is used.

The restrictions on the use of aggregate functions do not apply to scalar functions, because a scalar function is applied to a single value rather than to a set of values.

The result of the following SELECT statement has as many rows as there are employees in department D01:

   SELECT EMPNO, LASTNAME, YEAR(CURRENT DATE - BRTHDATE)
     FROM EMPLOYEE
     WHERE WORKDEPT = 'D01'

Scalar functions can be qualified with a schema name (for example, SYSIBM.CHAR(123)).

In a Unicode database, all scalar functions that accept a character or graphic string will accept any string types for which conversion is supported.