Nested functions

Scalar and aggregate functions can be nested in several ways.

You can nest functions in the following ways:

Scalar functions within scalar functions
Example: 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
```
Scalar functions within aggregate functions
In some cases, you might need to invoke a scalar function from within an aggregate function.
Example: 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.
Aggregate functions within scalar functions
Example: 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
```