You can use CASE expressions in SQL statements to easily manipulate the data representation of a table. This provides a powerful conditional expression capability that is similar in concept to CASE statements in some programming languages.
SELECT DEPTNAME, CASE DEPTNUMB WHEN 10 THEN 'Marketing' WHEN 15 THEN 'Research' WHEN 20 THEN 'Development' WHEN 38 THEN 'Accounting' ELSE 'Sales' END AS FUNCTION FROM ORG
The result is:
DEPTNAME FUNCTION -------------- ----------- Head Office Marketing New England Research Mid Atlantic Development South Atlantic Accounting Great Lakes Sales Plains Sales Pacific Sales Mountain Sales
SELECT LASTNAME, WORKDEPT FROM EMPLOYEE WHERE(CASE WHEN BONUS+COMM=0 THEN NULL ELSE SALARY/(BONUS+COMM) END ) > 10
The following example computes the ratio of the sum of the salaries of department 20 to the total of all salaries using a CASE expression:
SELECT CAST(CAST (SUM(CASE WHEN DEPT = 20 THEN SALARY ELSE 0 END) AS DECIMAL(7,2))/ SUM(SALARY) AS DECIMAL (3,2)) FROM STAFF
The result is 0.11. Note that the CAST functions ensure that the precision of the result is preserved.
CASE WHEN X<0 THEN -1 WHEN X=0 THEN 0 WHEN X>0 THEN 1 END
This expression has the same result as the SIGN user-defined function in the SYSFUN schema.