DB2 CASE expression

A CASE expression allows an SQL statement to be executed in one of 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.

Example: Suppose you want to display the employee number, name, and education level of all clerks in the employee table. Education levels are stored in the EDLEVEL column as small integers, but you would like to replace the values in this column with more descriptive phrases. An SQL statement like this accomplishes the task:

SELECT EMPNO, FIRSTNME, MIDINIT, LASTNAME,

CASE

WHEN EDLEVEL <= 12 THEN 'HIGH SCHOOL OR LESS'

WHEN EDLEVEL > 12 AND EDLEVEL <= 14 THEN 'JUNIOR COLLEGE'

WHEN EDLEVEL > 14 AND EDLEVEL <= 17 THEN 'FOUR-YEAR COLLEGE'

WHEN EDLEVEL > 17 THEN 'GRADUATE SCHOOL'

ELSE 'UNKNOWN'

END

AS EDUCATION

FROM DSN8510.EMP

WHERE JOB = 'CLERK';

The result table looks like this:

FIRSTNME MIDINIT LASTNAME   EDUCATION

======== ======= ========== =================

SEAN             O’Connell  JUNIOR COLLEGE

JAMES     J      JEFFERSON  JUNIOR COLLEGE

SALVATORE M      MARINO     FOUR-YEAR COLLEGE

DANIEL    S      SMITH      FOUR-YEAR COLLEGE

SYBIL     V      JOHNSON    FOUR-YEAR COLLEGE

MARIA     L      PEREZ      FOUR-YEAR COLLEGE

GREG             ORLANDO    JUNIOR COLLEGE

ROBERT    M      MONTEVERDE FOUR-YEAR COLLEGE

The CASE expression replaces each small integer value of EDLEVEL with a description of the amount of schooling each clerk received. If the value of EDLEVEL is null, then 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 those employees whose commission is more than a quarter of their salary, you could execute this SQL statement:

SELECT EMPNO, WORKDEPT, SALARY+COMM AS "SALARY+COMMISSION"

FROM DSN8510.EMP

WHERE COMM/SALARY > 0.25;

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

SELECT EMPNO, WORKDEPT, SALARY+COMM AS "SALARY+COMMISSION"

FROM DSN8510.EMP

WHERE (CASE WHEN SALARY = 0 THEN 0

ELSE COMM/SALARY

END) > 0.25;

The CASE expression determines the value that is to be compared to 0.25. If the salary is non-zero, then the ratio of salary to commission is used. If the salary is zero, then zero is used.