Ex: 01 : Assume that in the EMPLOYEE table the first character of a department number represents the division in the organization. Use a CASE expression to list the full name of the division to which each employee belongs.
SELECT EMPNO, LASTNAME,
WHEN 'A' THEN 'Administration'
WHEN 'B' THEN 'Human Resources'
WHEN 'C' THEN 'Operations'
FROM EMPLOYEE ;
Ex:02 : You can also use a CASE expression to avoid "division by zero" errors. From the EMPLOYEE table, find all employees who earn more than 25 percent of their income from commission, but who are not fully paid on commission:
SELECT EMPNO, WORKDEPT, SALARY+COMM FROM EMPLOYEE
WHERE (CASE WHEN SALARY=0 THEN 0
END) > 0.25;
Ex:03 : If the CASE expression is included in the SUM aggregate function, the CASE expression would prevent the errors. In the following query, the CASE expression screens out the unwanted division because the CASE operation is performed before the division.
SUM(CASE WHEN PAYMT_PAST_DUE_CT=0 THEN 0
WHEN PAYMT_PAST_DUE_CT>0 THEN
GROUP BY REF_ID,PAYMT_PAST_DUE_CT;
Ex:04 : This example shows how to group the results of a query by a CASE expression without having to re-type the expression. Using the sample employee table, find the maximum, minimum, and average salary. Instead of finding these values for each department, assume that you want to combine some departments into the same group.
FROM (SELECT SALARY,
CASE WHEN WORKDEPT = 'A00' OR WORKDEPT = 'E21' THEN 'A00_E21'
WHEN WORKDEPT = 'D11' OR WORKDEPT = 'E11' THEN 'D11_E11'
END AS CASE_DEPT FROM DSN8810.EMP) X
GROUP BY CASE_DEPT;