DB2® Universal Database for OS/390® Version 7.1 Certification Guide

CASE Expressions

You can add some 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 CUT_SCORE, you want to print 'Not Passed.' If it is between the CUT_SCORE 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 INTEGER(SCORE) < 65 THEN 'NOT PASSED' WHEN SCORE <= SCORE(90) THEN 'PASSED' ELSE 'EXCELLENT' END FROM CANDIDATE C, TEST_TAKEN TT ...