A Case for CASE
March 10, 2010 Skip Marchesani
|
The CASE operation is common to several programming languages on the IBM i. This includes SQL in DB2 for i, where it is available as the CASE statement for use in conjunction with a SELECT statement, or CASE expression for use in conjunction with the DB2 for i SQL Procedure Language. If you aren’t familiar with CASE from an SQL perspective, it’s a really effective mechanism to easily handle one or more forms of simple or complex conditional processing within the same SQL statement. The Two Forms of CASE There are two forms of CASE: Simple WHEN clause and searched WHEN clause, and the syntax for either form is pretty straightforward. Here’s the syntax of the simple WHEN clause: CASE target-expression WHEN condition-1 THEN result-expression-1 WHEN condition-2 THEN result-expression-2 … WHEN condition-n THEN result-expression-n (at least one WHEN clause required) ELSE else-expression (ELSE clause is optional) END CASE or END (use END in conjunction with AS to name the result-expression) And here’s the searched WHEN clause: CASE WHEN search-expression-1 THEN result-expression-1 WHEN search-expression-2 THEN result-expression-2 … WHEN search-condition-n THEN result-expression-n (at least one WHEN clause required) ELSE else-expression (ELSE clause is optional) END CASE or END (use END in conjunction with AS to name the result-expression) To illustrate the two forms of CASE, let’s look at a couple of examples using an employee master file that contains the following columns and rows. Nbr Nam Cls Sal 20 Heikki 2 6000 10 Ed 5 7000 50 Marcela 3 7500 40 Mike 4 6500 30 John 5 3200 60 Frank 2 6500 The employee job classification number (Cls) refers to the employee job title. I want to execute a query (SELECT statement) that includes the actual job title name in the result set. Since the job title is not available in my database, the CASE statement provides the mechanism to easily include the job title name. The relationship between the job classification number (Cls) and the job title name is shown in the following table. Cls Job Title 2 Senior Programmer 3 Project Manager 4 Department Manager 5 Programmer The following example includes job title in the result set using a simple WHEN clause:
SELECT nbr, nam, cls, sal,
CASE cls
WHEN 2 THEN 'Senior Programmer'
WHEN 3 THEN 'Project Manager'
WHEN 4 THEN 'Department Manager'
WHEN 5 THEN 'Programmer'
ELSE 'No Title'
END AS Job_Title
FROM emp
ORDER BY nbr;
The following example includes job title in the result set using a searched WHEN clause:
SELECT nbr, nam, cls, sal,
CASE
WHEN cls = 2 THEN 'Senior Programmer'
WHEN cls = 3 THEN 'Project Manager'
WHEN cls = 4 THEN 'Department Manager'
WHEN cls = 5 THEN 'Programmer'
ELSE 'No Title'
END AS Job_Title
FROM emp
ORDER BY nbr;
Both the simple and searched WHEN clauses yield the same result set as shown below. Nbr Nam Cls Sal Job Title 10 Ed 5 7000 Programmer 20 Heikki 2 6000 Senior Programmer 30 John 5 3200 Programmer 40 Mike 4 6500 Department Manager 50 Marcela 3 7500 Project Manager 60 Frank 2 6500 Senior Programmer The ELSE Clause In both the previous examples, for each job classification (Cls) in the employee table, there was a corresponding match for job classification in the CASE statement. So what happens if there is no corresponding match in the CASE statement? If employee 30, John, had a job classification (Cls) of 6 instead of 5, each WHEN clause would be sequentially tested to see if there was a match for 6. Since there would be no match in the four WHEN clauses, the ELSE clause would provide the default expression that would be applied. In this case, employee 30, John, would have a job title of “No Title” as shown below since the ELSE clause expression contains “No Title”. Nbr Nam Cls Sal Job Title 10 Ed 5 7000 Programmer 20 Heikki 2 6000 Senior Programmer 30 John 6 3200 No Title 40 Mike 4 6500 Department Manager 50 Marcela 3 7500 Project Manager 60 Frank 2 6500 Senior Programmer As stated earlier in this article, the ELSE clause is optional. If we have the above scenario where there is no match in the WHEN clause(s) and there is no ELSE clause to provide a default expression, what happens? The answer is simple. Employee 30, John, receives a null job title, indicated by a hyphen (-), as shown below. Nbr Nam Cls Sal Job Title 10 Ed 5 7000 Programmer 20 Heikki 2 6000 Senior Programmer 30 John 6 3200 - 40 Mike 4 6500 Department Manager 50 Marcela 3 7500 Project Manager 60 Frank 2 6500 Senior Programmer Pivoting the Result Set An IBM DB2 developer in Rochester showed me how to use CASE to pivot results from vertical to horizontal formatting, i.e., how to transform results that run from top to bottom down the page to results running left to right across the page. The following SELECT statement retrieves a result set that totals the salary for each department and then sums the salary for all departments in the last line with department number 999.
SELECT dpt, SUM(sal) AS dpt_sal
FROM emp
GROUP BY dpt
UNION ALL
SELECT '999' AS dpt, SUM(sal) AS dpt_sal
FROM emp
ORDER BY dpt;
Dpt Dpt_Sal 901 6000 911 14500 977 9700 990 6500 999 36700 The formatting for this result set is vertical from top to bottom running down the page. Instead of the results running top to bottom, I would like them to be horizontal from left to right running across the page. Using the following syntax CASE can do this for me.
SELECT SUM(sal) AS Tot_Sal,
SUM(CASE WHEN dpt = 901 THEN sal END) as dpt_901,
SUM(CASE WHEN dpt = 911 THEN sal END) as dpt_911,
SUM(CASE WHEN dpt = 977 THEN sal END) as dpt_977,
SUM(CASE WHEN dpt = 990 THEN sal END) as dpt_990
FROM emp;
Tot_Sal Dpt_901 Dpt_911 Dpt_977 Dpt_990 36700 6000 14500 9700 6500 CASE used in this manner allows the derivation of a specific column in my result set based on the condition or search expression in the WHEN clause–a neat trick that provides additional flexibility when constructing SQL queries. Case Considerations Let me close with a few miscellaneous points. CASE may be used in an SQL statement anywhere an SQL expression is allowed. The WHEN clause can contain any expression that is valid within a WHERE clause. CASE statements can be nested with no limits on the number of nested levels for a Searched WHEN clause, and a limit of three nested levels for a Simple WHEN clause. Skip Marchesani retired from IBM after 30 years and is now a consultant with Custom Systems Corporation. He is also a founding partner of System i Developer and the RPG & DB2 Summit. Skip spent much of his IBM career working with the Rochester Development Lab on projects for S/38 and AS/400 and was involved with the development of the AS/400. He was part of the team that taught early AS/400 education to customers and IBM lab sites worldwide. Skip is recognized as an industry expert on DB2 for i and the author of the book DB2/400: The New AS/400 Database. He specializes in providing customized education for any area of the System i, iSeries, and AS/400; does database design and design reviews; and performs general System i, iSeries, and AS/400 consulting for interested clients. He has been a speaker for user groups, technical conferences, and System i, iSeries, and AS/400 audiences around the world. He is an award-winning COMMON speaker and has received its Distinguished Service Award. Send your questions or comments for Skip to Ted Holt via the IT Jungle Contact page.
|
