21
Fri, Jun
3 New Articles

The Case for SQL CASE

SQL
Typography
  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times

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

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.

BLOG COMMENTS POWERED BY DISQUS

LATEST COMMENTS

Support MC Press Online

$0.00 Raised:
$

Book Reviews

Resource Center

  • SB Profound WC 5536 Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application. You can find Part 1 here. In Part 2 of our free Node.js Webinar Series, Brian May teaches you the different tooling options available for writing code, debugging, and using Git for version control. Brian will briefly discuss the different tools available, and demonstrate his preferred setup for Node development on IBM i or any platform. Attend this webinar to learn:

  • SB Profound WP 5539More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.

  • SB HelpSystems ROBOT Generic IBM announced the E1080 servers using the latest Power10 processor in September 2021. The most powerful processor from IBM to date, Power10 is designed to handle the demands of doing business in today’s high-tech atmosphere, including running cloud applications, supporting big data, and managing AI workloads. But what does Power10 mean for your data center? In this recorded webinar, IBMers Dan Sundt and Dylan Boday join IBM Power Champion Tom Huntington for a discussion on why Power10 technology is the right strategic investment if you run IBM i, AIX, or Linux. In this action-packed hour, Tom will share trends from the IBM i and AIX user communities while Dan and Dylan dive into the tech specs for key hardware, including:

  • Magic MarkTRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now!  Request Now.

  • SB HelpSystems ROBOT GenericForms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.

  • SB HelpSystems ROBOT GenericIT security is a top priority for businesses around the world, but most IBM i pros don’t know where to begin—and most cybersecurity experts don’t know IBM i. In this session, Robin Tatam explores the business impact of lax IBM i security, the top vulnerabilities putting IBM i at risk, and the steps you can take to protect your organization. If you’re looking to avoid unexpected downtime or corrupted data, you don’t want to miss this session.

  • SB HelpSystems ROBOT GenericCan you trust all of your users all of the time? A typical end user receives 16 malicious emails each month, but only 17 percent of these phishing campaigns are reported to IT. Once an attack is underway, most organizations won’t discover the breach until six months later. A staggering amount of damage can occur in that time. Despite these risks, 93 percent of organizations are leaving their IBM i systems vulnerable to cybercrime. In this on-demand webinar, IBM i security experts Robin Tatam and Sandi Moore will reveal:

  • FORTRA Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:

  • FORTRAManaging messages on your IBM i can be more than a full-time job if you have to do it manually. Messages need a response and resources must be monitored—often over multiple systems and across platforms. How can you be sure you won’t miss important system events? Automate your message center with the Robot Message Management Solution. Key features include:

  • FORTRAThe thought of printing, distributing, and storing iSeries reports manually may reduce you to tears. Paper and labor costs associated with report generation can spiral out of control. Mountains of paper threaten to swamp your files. Robot automates report bursting, distribution, bundling, and archiving, and offers secure, selective online report viewing. Manage your reports with the Robot Report Management Solution. Key features include:

  • FORTRAFor over 30 years, Robot has been a leader in systems management for IBM i. With batch job creation and scheduling at its core, the Robot Job Scheduling Solution reduces the opportunity for human error and helps you maintain service levels, automating even the biggest, most complex runbooks. Manage your job schedule with the Robot Job Scheduling Solution. Key features include:

  • LANSA Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.

  • LANSAWhen it comes to creating your business applications, there are hundreds of coding platforms and programming languages to choose from. These options range from very complex traditional programming languages to Low-Code platforms where sometimes no traditional coding experience is needed. Download our whitepaper, The Power of Writing Code in a Low-Code Solution, and:

  • LANSASupply Chain is becoming increasingly complex and unpredictable. From raw materials for manufacturing to food supply chains, the journey from source to production to delivery to consumers is marred with inefficiencies, manual processes, shortages, recalls, counterfeits, and scandals. In this webinar, we discuss how:

  • The MC Resource Centers bring you the widest selection of white papers, trial software, and on-demand webcasts for you to choose from. >> Review the list of White Papers, Trial Software or On-Demand Webcast at the MC Press Resource Center. >> Add the items to yru Cart and complet he checkout process and submit

  • Profound Logic Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application.

  • SB Profound WC 5536Join us for this hour-long webcast that will explore:

  • Fortra IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn: