Saturday, November 17, 2012

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.


No comments:

Post a Comment

Note: Only a member of this blog may post a comment.