SQL CASE expressions are powerful. Understand their syntax and where they can be used.

Did you know that the SQL CASE construct can be used in SQL SELECT and UPDATE statements and in WHERE, GROUP BY, and ORDER BY clauses? This tip explains SQL CASE through examples that you can run and experiment with. It also gives an overview of SQL CASE syntax, though it does not attempt to rewrite the IBM DB2 for i SQL Reference manual.

SQL CASE Syntax

There are SQL CASE expressions and SQL CASE statements. An expression can be slotted in where a value is needed. A statement allows you to execute different SQL statements, usually UPDATE or DELETE statements. In this tip, I will cover only SQL CASE expressions.

An SQL CASE construct consists of one or more "WHEN conditions THEN result" parts and an optional "ELSE result" part.

There can be a simple when clause, where the value is set before the first WHEN keyword is executed, or a searched when clause, where the value is determined as the WHEN keyword is executed.

Example of a Simple When Clause

  case state

      when 'CA' then 'California'

      when 'TX' then 'Texas'

      else 'Also Rans'

  end

In this example, all the decisions are based on the value in field "state," which is determined before the first WHEN is executed.

Example of a Searched When Clause

  case when City = 'Hector' then 150

       when Zipcod > 90000 then 200

       else 300

  end

In this example, decisions are made on the City field and the Zipcod field, which are evaluated when each WHEN is executed.

Both of the above examples are SQL CASE expressions, in that they provide values.

Running the Examples

The examples in this tip are based on file QIWS/QCUSTCDT, which I believe by default is available on almost everyone's machine, unless you have deleted it. It contains just 12 records and some of the data looks like this:

CUSNUM   LSTNAM    CITY    STATE  ZIPCOD   CDTLMT     BALDUE

938,472   Henning   Dallas   TX    75,217    5,000      37.00

839,283   Jones     Clay     NY    13,041      400     100.00

392,859   Vine      Broton   VT     5,046      700     439.00

938,485   Johnson   Helen    GA    30,545    9,999   3,987.50

397,267   Tyron     Hector   NY    14,841    1,000        .00

389,572   Stevens   Denver   CO    80,226      400      58.75

846,283   Alison    Isle     MN    56,342    5,000      10.00

475,938   Doe       Sutter   CA    95,685      700     250.00

693,829   Thomas    Casper   WY    82,609    9,999        .00

593,029   Williams  Dallas   TX    75,218      200      25.00

192,837   Lee       Hector   NY    14,841      700     489.50

583,990   Abraham   Isle     MN    56,342    9,999     500.00

To run any of the examples, cut and paste into your favorite SQL client. I have formatted the code for clarity, but be aware that your client's paste function may not retain the formatting I have used. Sometimes words may run together, especially in green-screen clients. To avoid this problem, I have tried to keep a blank at the beginning of each code line, which hopefully will make it through the conversion to HTML. I expect you will have fewer difficulties if you use Run SQL Scripts in iSeries Navigator or another GUI client, such as the open-source SQuirreL.

SQL CASE in SELECT Statements

Let's say we want to spell out some of the state names and put in a default value for the others. We could use a SELECT statement like this, with a simple when clause:

 Select  state,

         case state

            when 'CA' then 'California'

            when 'NY' then 'New York'

            when 'TX' then 'Texas'

            else 'Also Rans'

         end StateName

 from    qiws/qcustcdt

We would get this result:

  STATE  STATENAME  

   TX    Texas      

   NY    New York   

   VT    Also Rans  

   GA    Also Rans  

   NY    New York   

   CO    Also Rans  

   MN    Also Rans  

   CA    California 

   WY    Also Rans  

   TX    Texas      

   NY    New York   

   MN    Also Rans  

Here, the SQL CASE builds a new column (field) from a constant. "StateName" after the "end" gives a name to the column we generated.

Let's look at a searched when clause. We want to count how many customers there are in each of three balance-due ranges: up to $250, $250–$500, and over $500. This select statement does the job:

 SELECT

    sum(case when baldue between    .01 and 250 then 1 else 0 end) Lt250,

    sum(case when baldue between 250.01 and 500 then 1 else 0 end) Lt500,

    sum(case when baldue >= 500.01              then 1 else 0 end) Other

 FROM qiws/qcustcdt

Each "when" sets a value of either 1 or 0 for the enclosing "sum" function. It may look a bit strange, but if balance due is $37.00, then the first "when" resolves to "sum(1) as Lt250."

The result of the SELECT is a single row:

        LT250           LT500           OTHER

            6               3               1

SQL CASE in a SELECT Statement WHERE Clause

Maybe we want to see customers whose balance due is greater than 35 percent of their credit limit, with the added wrinkle that in New York it is 40 percent of their credit limit. We can put that into the WHERE clause, like this:

 Select   cusnum, lstnam, state, cdtlmt, baldue,

          decimal(baldue/cdtlmt*100,7,2) bal_ratio

 from     qiws/qcustcdt

 where    baldue > cdtlmt *

             case when state = 'NY' then .40

                  else                   .35

             end

 order by state

 This result is produced:

  CUSNUM   LSTNAM    STATE  CDTLMT     BALDUE   BAL_RATIO

 475,938   Doe        CA       700     250.00       35.71

 938,485   Johnson    GA     9,999   3,987.50       39.87

 192,837   Lee        NY       700     489.50       69.92

 392,859   Vine       VT       700     439.00       62.71

SQL CASE in an Order By Clause

We want a list of customers by state, showing descending balances due in each state, but with the exception that we want Texas first, followed by California, then New York, and finally all the rest by state.

In this SELECT statement, the first field in the ORDER BY clause is unnamed and is generated by the SQL CASE expression:

 Select   state, cusnum, lstnam, baldue

 from     qiws/qcustcdt

 order by case state

             when 'TX' then 1

             when 'CA' then 2

             when 'NY' then 3

             else         999

          end, state, baldue desc

These are the results:

  STATE   CUSNUM   LSTNAM      BALDUE

   TX    938,472   Henning      37.00

   TX    593,029   Williams     25.00

   CA    475,938   Doe         250.00

   NY    192,837   Lee         489.50

   NY    839,283   Jones       100.00

   NY    397,267   Tyron          .00

   CO    389,572   Stevens      58.75

   GA    938,485   Johnson   3,987.50

   MN    583,990   Abraham     500.00

   MN    846,283   Alison       10.00

   VT    392,859   Vine        439.00

   WY    693,829   Thomas         .00

SQL CASE in a Group By Clause

You can also code SQL CASE in a group by clause. If we wanted to see our total exposure for balances up to $250, balances $250–$500, and balances above $500, we could run an SQL statement like this:

 SELECT

    case when baldue between    .01 and 250 then '0-250'

         when baldue between 250.01 and 500 then '251-500'

         else '500+'

    end Bal_Range,

    sum(baldue) Exposure

 FROM qiws/qcustcdt

 WHERE baldue > 0 

 group by

    case when baldue between    .01 and 250 then '0-250'

         when baldue between 250.01 and 500 then '251-500'

         else '500+'

    end

 order by 1

It produces results like this:

   BAL_RANGE                                   EXPOSURE

    0-250                                        480.75

    251-500                                    1,428.50

    500+                                       3,987.50

This isn't a perfect solution, because you need to repeat the SQL CASE code. This following code gives the same results using a Common Table Expression. It still uses SQL CASE, but not in the GROUP BY clause.

 with mydata as (

   select

      case when baldue between    .01 and 250 then '0-250'

           when baldue between 250.01 and 500 then '251-500'

           else                                    '500+'

     end Bal_Range,

    baldue

   FROM qiws/qcustcdt

 )

 select   Bal_range, sum(baldue) Exposure

 from     myData

 where    baldue > 0

 group by Bal_Range

 order by 1

SQL CASE in an Update Statement

To encourage settlement of balances due, we might decide to give those in Texas a 10 percent discount, those in California a 20 percent discount, and everyone else a 30 percent discount. We can do this with an SQL CASE expression in the SET clause of an update statement.

First, for this demo, let's create temporary table MyDemo so we don't update QIWS/QCUSTCDT:

  create table qtemp/MyDemo as

  (Select * FROM qiws/qcustcdt) with data

 Now we can run this code where SQL CASE provides the discount percentage based on the state:

 update qtemp/MyDemo

 set baldue = baldue - baldue *

    case state

       when 'TX' then .10

       when 'CA' then .20

       else           .30

    end

 where baldue > 0

 This will update 10 rows.

Conclusion

The SQL CASE construct is powerful and convenient. It is well worth becoming familiar with its capabilities and syntax. There is not room in this tip to cover everything it can do, but you should have enough information to experiment further. If you're ambitious, try using SQL CASE in a JOIN predicate.

Notes

I ran these examples on the free V5R3 machine at http://www.rzkh.de, but SQL CASE has been available since at least V4R5, so most should be able to use it.

I have formatted the example code because I find it much easier to read and debug. However, SQL does not require such formatting.

Sam Lennon is an analyst, developer, consultant and IBM i geek. He started his programming career in 360 assembly language on IBM mainframes, but moved to the AS400 platform in 1991 and has been an AS400/iSeries/i5/IBM i advocate ever since.

Also Read

SQL 101: DDL Recap—From Physical Files to SQL Tables, Starting the Journey

TechTip: Generate and Email a Spreadsheet with SQL

Practical SQL: Journal Forensics Using SQL