This section gives you a brief introduction to functions that will be used in the examples throughout the book. A database function is a relationship between a set of input data values and a result value.
Functions can be either built-in or user-defined. DB2 Universal Database delivers many built-in and preinstalled user-defined functions. You can find the built-in functions in the SYSIBM schema and the preinstalled user-defined functions in the SYSFUN schema. SYSIBM and SYSFUN are reserved schemas.
The built-in and preinstalled user-defined functions will never satisfy all requirements. So application developers may need to create their own suite of functions specific to their applications. User-defined functions make this possible, expanding the scope of DB2 Universal Database to include, for example, customized business or scientific functions. This is further discussed in the User-Defined Functions.
Column functions operate on a set of values in a column to derive a single result value. The following are just a few examples of column functions. For a full list refer to the SQL Reference.
The following statement selects the maximum salary from the STAFF table:
SELECT MAX(SALARY)
FROM STAFF
This statement returns the value 22959.20 from the STAFF sample table.
The next example selects the names and salaries of employees whose income is more than the average income yet have been with the company less than the average number of years.
SELECT NAME, SALARY
FROM STAFF
WHERE SALARY > (SELECT AVG(SALARY) FROM STAFF)
AND YEARS < (SELECT AVG(YEARS) FROM STAFF)
This statement produces the following result:
NAME SALARY
--------- ---------
Marenghi 17506.75
Daniels 19260.25
Gonzales 16858.20
In the above example, in the WHERE clause, the column function is stated in a subquery as opposed to being directly implemented (WHERE SALARY > AVG(SALARY)). Column functions cannot be stated in the WHERE clause. This is due to the order of operations. The WHERE clause can be thought of as being evaluated before the SELECT clause. Consequently, when the WHERE clause is being evaluated, the column function does not have access to the set of values. This set of values are selected at a later time by the SELECT clause.
You can specify DISTINCT as part of the argument of a column function to eliminate duplicate values before a function is applied. Thus, COUNT(DISTINCT WORKDEPT) computes the number of different departments.
A scalar function performs some operation on a value to return another value. The following are just a few examples of scalar functions provided by DB2 Universal Database.
For a detailed list and description of scalar functions refer to the SQL Reference.
The following statement returns the department names from the ORG table together with the length of each of these names:
SELECT DEPTNAME, LENGTH(DEPTNAME)
FROM ORG
This statement produces the following result:
DEPTNAME 2
-------------- -----------
Head Office 11
New England 11
Mid Atlantic 12
South Atlantic 14
Great Lakes 11
Plains 6
Pacific 7
Mountain 8
Note that since the AS clause was not used to give a meaningful name to LENGTH(DEPTNAME), a system generated number appears in the second column.