CASE expressions

You can use a CASE expression to execute SQL expressions in several different ways depending on the value of a search condition.

One use of a CASE expression is to replace the values in a result table with more meaningful values.

Begin general-use programming interface information.

Example: Suppose that you want to display the employee number, name, and education level of all field representatives in the EMP table. Education levels are stored in the EDL column as small integers, but you want to replace the values in this column with more descriptive phrases. Use the following query:

SELECT EMPNO, FIRSTNME, LASTNAME,
  CASE
    WHEN EDL<=12 THEN 'HIGH SCHOOL OR LESS'
    WHEN EDL>12 AND EDL<=14 THEN 'JUNIOR COLLEGE'
    WHEN EDL>14 AND EDL<=17 THEN 'FOUR-YEAR COLLEGE'
    WHEN EDL>17 THEN 'GRADUATE SCHOOL'
    ELSE 'UNKNOWN'
  END
  AS EDUCATION
  FROM EMP
  WHERE JOB='FLD';

The result table looks like following example:

EMPNO      FIRSTNME     LASTNAME     EDUCATION
======     ========     ========     =================
000320     RAMLAL       MEHTA        FOUR-YEAR COLLEGE
000330     WING         LEE          JUNIOR COLLEGE
200340     ROY          ALONZO       FOUR-YEAR COLLEGE

The CASE expression replaces each small integer value of EDL with a description of the amount of each field representative's education. If the value of EDL is null, the CASE expression substitutes the word UNKNOWN.

Another use of a CASE expression is to prevent undesirable operations, such as division by zero, from being performed on column values.

Example: If you want to determine the ratio of employee commissions to their salaries, you can execute this query:

SELECT EMPNO, DEPT,
  COMM/SALARY AS "COMMISSION/SALARY",
FROM EMP;

This SELECT statement has a problem, however. If an employee has not earned any salary, a division-by-zero error occurs. By modifying the following SELECT statement with a CASE expression, you can avoid division by zero:

SELECT EMPNO, DEPT,
  (CASE WHEN SALARY=0 THEN NULL
    ELSE COMM/SALARY
   END) AS "COMMISSION/SALARY"
  FROM EMP;

The CASE expression determines the ratio of commission to salary only if the salary is not zero. Otherwise, DB2® sets the ratio to a null value.

End general-use programming interface information.