The argument of a column function is a set of values derived from an expression. The expression may include columns but cannot include a scalar-fullselect or another column function (SQLSTATE 42607). The scope of the set is a group or an intermediate result table as explained in Queries.
If a GROUP BY clause is specified in a query and the intermediate result from the FROM, WHERE, GROUP BY and HAVING clauses is the empty set; then the column functions are not applied, the result of the query is the empty set, the SQLCODE is set to +100 and the SQLSTATE is set to '02000'.
If a GROUP BY clause is not specified in a query and the intermediate result is of the FROM, WHERE, and HAVING clauses is the empty set, then the column functions are applied to the empty set.
For example, the result of the following SELECT statement is the number of distinct values of JOBCODE for employees in department D01:
SELECT COUNT(DISTINCT JOBCODE)
FROM CORPDATA.EMPLOYEE
WHERE WORKDEPT = 'D01'
The keyword DISTINCT is not considered an argument of the function, but rather a specification of an operation that is performed before the function is applied. If DISTINCT is specified, duplicate values are eliminated. If ALL is implicitly or explicitly specified, duplicate values are not eliminated.
Expressions can be used in column functions, for example:
SELECT MAX(BONUS + 1000)
INTO :TOP_SALESREP_BONUS
FROM EMPLOYEE
WHERE COMM > 5000
The column functions that follow are in the SYSIBM schema and may be qualified with the schema name (for example, SYSIBM.COUNT(*)).