Nested functions

Scalar and aggregate functions can be nested in several ways.

You can nest functions in the following ways:

Begin general-use programming interface information.

Example: Scalar functions within scalar functions

Suppose that you want to know the month and day of hire for a particular employee in department D11. Suppose that you also want the result in USA format (mm/dd/yyyy). Use this query:

SELECT SUBSTR((CHAR(HIREDATE, USA)),1,5)
  FROM EMP
  WHERE LASTNAME = 'BROWN' AND DEPT = 'D11';

The result table looks like this:

=====
03/03

Example: Scalar functions within aggregate functions

In some cases, you might need to invoke a scalar function from within an aggregate function. Suppose that you want to know the average number of years of employment for employees in department A00. Use this query:

SELECT AVG(DECIMAL(YEAR(CURRENT DATE - HIREDATE)))
  FROM EMP
  WHERE DEPT = 'A00';

The result table looks like this:

=======
20.6666

The actual form of the result, 20.6666, depends on how you define the host variable to which you assign the result.

Example: Aggregate functions within scalar functions

Suppose that you want to know the year in which the last employee was hired in department E21. Use this query:

SELECT YEAR(MAX(HIREDATE))
  FROM EMP
  WHERE DEPT = 'E21';

The result table looks like this:

====
2002

End general-use programming interface information.