SQL Getting Started

Case Expressions

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.

To change department numbers from the DEPTNAME column in ORG table to meaningful words, enter the following query:

     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

You can use CASE expressions to protect against exceptions such as division by zero. In the following example, if the employee has no bonus or commission payment, the statement condition prevents an error by avoiding the division operation:

 
     SELECT LASTNAME, WORKDEPT FROM EMPLOYEE
        WHERE(CASE
                 WHEN BONUS+COMM=0 THEN NULL
                 ELSE SALARY/(BONUS+COMM)
              END ) > 10

You can use a CASE expression to produce a ratio based on the sum of a subset of values from one column to the sum of all the values from that column in a single statement. A statement using a CASE expression requires only a single pass through the data. Without a CASE expression, at least two passes are required to perform the same calculation.
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.
You can use a CASE expression to evaluate a simple function instead of calling the function itself, which would require additional overhead. For example:
```
     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.

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]