CASE Expressions

Team-Fly

---

 

	DB2 Universal Database for OS/390 v7.1 Application Certification Guide By Susan Lawson
	Table of Contents
	Chapter 4.  Advanced SQL Coding

---

CASE Expressions

You can add ifthenelse logic to your SQL statements and output using CASE expressions. Consider the generation of a list of those candidates who have passed the DB2 Fundamentals exam. In the report, you want to print the score of the tests, but instead of printing the numeric score, you want to print a message. If the score is below the 65, you want to print Not Passed. If it is between the 65 and 90, you want to print Passed, and if the score is above 90, you want to print Excellent. The following SQL statement using a CASE expression accomplishes this:

 SELECT fname,lname,     CASE       WHEN SCORE < 65 THEN 'Not Passed'       WHEN SCORE <= 90 THEN 'Passed '       ELSE       'Excellent'    END AS TEST_RESULT FROM candidate c, test_taken tt WHERE c.cid=tt.cid AND TT.number='500' 

The SQL statement presented above provides string messages based on the conditions of the CASE expression. In this example, the score column features a numeric value, but we use it to produce a character string. The column derived from the CASE expression has been assigned the name TEST_RESULT.

The order of the conditions for the CASE expression is very important. DB2 will process the first condition first, then the second, and so on. If you do not pay attention to the order in which the conditions are processed , you might be retrieving the same result for every row in your table. For example, if you coded the <= 90 option before the < 65, all the data that is lower than 91, even 64 or 30, will display the message "Passed".

CASE expressions can be used in places other than select lists, such as WHERE, ON, and HAVING clauses. CASE expressions can also be nested within other CASE expressions.

NOTE

You must use the END keyword to finish a CASE statement.

Using CASE Expressions in Functions

CASE expressions can be embedded as function parameters. This allows you to pass different parameters to the function in a single pass of the data. Suppose that the TEST_TAKEN table is very large and we have a requirement to count the following:

• The number of tests taken with a score higher than 90

• The number of tests taken with a score of 90

• The number of tests taken with a score lower than 70

• The number of DB2 Fundamentals exams taken

Without the use of CASE expressions, this will require four different queries that will potentially read the entire table. We want to do this in a single pass of the data, because the table is very large. The query will use four SUM functions to simulate the COUNT function, each one evaluating different criteria using a CASE expression.

 SELECT SUM (CASE WHEN score > 90 then 1     ELSE null    END) AS moregb90,    SUM (CASE WHEN score = 90 then 1    ELSE null    END) AS equalgb90,    SUM (CASE WHEN score < 70 then 1    ELSE null    END) AS minorgb70,    SUM (CASE WHEN number='500' then 1    ELSE null    END) AS equalgb500 FROM test_taken WHERE score >= 90    OR score < 70    OR number = '500'; 

This type of query may be useful when performing data inspection analysis. Notice that the four different requirements are solved in a single pass of the data. The query was created using a different SUM function for each one of the conditions presented as a requirement. The conditions are evaluated in the CASE expression inside each function. When the condition evaluates true, it will return a value of 1 and the row will be summed. When the condition evaluates false, the case expression will return a null value and the row will not be summed. The SUM function is utilized to simulate a count, because the COUNT function cannot contain an expression.

NOTE

It is important for this type of example to remember to code the WHERE clause predicate. Although rows that do not qualify will not affect the result of the SUM functions, DB2 will still apply the CASE expression to these rows. If these rows are excluded by the WHERE clause, then there will be a performance improvement by avoiding unnecessary CASE expressions against these rows.

---

Team-Fly

Top