CASE expressions in SQL

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,
CASE SUBSTR(WORKDEPT,1,1)
WHEN 'A' THEN 'Administration'
WHEN 'B' THEN 'Human Resources'
WHEN 'C' THEN 'Operations'
END
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
ELSE COMM/(SALARY+COMM)
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.

SELECT REF_ID,PAYMT_PAST_DUE_CT,
SUM(CASE WHEN PAYMT_PAST_DUE_CT=0 THEN 0
WHEN PAYMT_PAST_DUE_CT>0 THEN
BAL_AMT/PAYMT_PAST_DUE_CT
END)
FROM PAY_TABLE
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.

SELECT CASE_DEPT,MAX(SALARY),MIN(SALARY),AVG(SALARY)
FROM (SELECT SALARY,
CASE WHEN WORKDEPT = 'A00' OR WORKDEPT = 'E21' THEN 'A00_E21'
WHEN WORKDEPT = 'D11' OR WORKDEPT = 'E11' THEN 'D11_E11'
ELSE WORKDEPT
END AS CASE_DEPT FROM DSN8810.EMP) X
GROUP BY CASE_DEPT;