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.
LATEST COMMENTS
MC Press Online