It is now two and a half year since my former partner wrote a tip about the CASE expression in SQL. I often end up helping people with their SQL statements and when I use the CASE expression, they haven't seen it before. So it is time to reprint it, now in english.

The CASE expression was added to the DB2 SQL syntax in version 5. The CASE expression comes in two flavours, the "simple WHEN" and the "searched WHEN". Let's start with the syntax of the "simple WHEN":

>--- CASE expression --->

  <-------------------------------------------+
>--- WHEN expression THEN result-expression ------>

  +--- ELSE result-expression ---+
>------------------------------------ END

And here is an example of its use:

SELECT PGM_NAME
     , CASE PGM_LANG
       WHEN 'C2' THEN 'COBOL2'
       WHEN 'P1' THEN 'PL/1'
       WHEN 'AS' THEN 'ASSEMBLER'
       ELSE 'NOT USED HERE'
       END
FROM PGM_TABLE

If the ELSE is omitted and column PGM_LANG contains other values than C2, P1 or AS, the CASE expression returns the NULL value. Before version 5 it was common to use a UNION to produce the same result set as in the example, but it had a cost of three extra scans of the PGM_TABLE table. Now let's look at the syntax of a "searched WHEN":

>--- CASE --->

  <-------------------------------------------------+
>--- WHEN search-condition THEN result-expression ------>

  +--- ELSE result-expression ---+
>------------------------------------ END

And (big surprise) now an example:

SELECT PGM_NAME
     , CASE
       WHEN CURRENT DATE > ENDDATE THEN 'EXPIRED'
       WHEN CURRENT DATE < STARTDATE THEN 'WAITING'
       ELSE 'ACTIVE'
       END
FROM PGM_TABLE

In this example I translate the status of all the programs to a meaningful value based on a date interval. This is an ideal task for the "searched WHEN" expression and could not be solved with the "simple WHEN" expression.

I have shown examples where the CASE expression is used only in the SELECT list, but it can be used anywhere in the SQL-statement, where you can use an expression. When used in the WHERE-clause you must be careful, because it can or will influence your access path and maybe lead to unwanted response times.