Scalar functions

DB2® offers many different scalar functions, including the CHAR, DECIMAL, and NULLIF scalar functions.

Like an aggregate function, a scalar function produces a single value. Unlike the argument of an aggregate function, an argument of a scalar function is a single value.

Begin general-use programming interface information.

Example: YEAR: This query, which uses the YEAR scalar function, returns the year in which each employee in a particular department was hired:

SELECT YEAR(HIREDATE) AS HIREYEAR
  FROM EMP
  WHERE DEPT = 'A00';

The result table looks like this:

HIREYEAR
========
    1975
    1990
    1985

The YEAR scalar function produces a single scalar value for each row of EMP that satisfies the search condition. In this example, three rows satisfy the search condition, so YEAR results in three scalar values.

DB2 offers many different scalar functions, including CHAR, DECIMAL, and NULLIF.

CHAR

The CHAR function returns a string representation of the input value.

Example: CHAR: The following SQL statement sets the host variable AVERAGE to the character string representation of the average employee salary:

SELECT CHAR(AVG(SALARY))
  INTO :AVERAGE
  FROM EMP;

DECIMAL

The DECIMAL function returns a decimal representation of the input value.

Example: DECIMAL: Assume that you want to change the decimal data type to return a value with a precision and scale that you prefer. The following example represents the average salary of employees as an eight-digit decimal number (the precision) with two of these digits to the right of the decimal point (the scale):

SELECT DECIMAL(AVG(SALARY),8,2)
  FROM EMP;

The result table looks like this:

==========
  32602.30

NULLIF

NULLIF returns a null value if the two arguments of the function are equal. If the arguments are not equal, NULLIF returns the value of the first argument.

Example: NULLIF: Suppose that you want to calculate the average earnings of all employees who are eligible to receive a commission. All eligible employees have a commission of greater than 0, and ineligible employees have a value of 0 for commission:

SELECT AVG(SALARY+NULLIF(COMM,0))
  AS "AVERAGE EARNINGS"
  FROM EMP;

The result table looks like this:

AVERAGE EARNINGS
================
   35248.8461538

End general-use programming interface information.

Specifying a simple expression for the sum of the salary and commission in the select list would include all employees in the calculation of the average. To avoid including those employees who do not earn a commission in the average, you can use the NULLIF function to return a null value instead. The result of adding a null value for the commission to SALARY is itself a null value, and aggregate functions, like AVG, ignore null values. Therefore, this use of NULLIF inside AVG causes the query to exclude each row in which the employee is not eligible for a commission.