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.
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.
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.