CASE is such a versatile instrument if you know how to use it. In the previous article, I covered one possible use: decoding the values stored in the database into something more understandable to end users. Now let’s see what else CASE can do.

Written by Rafael Victoria-Pereira

Let’s start with something incredibly useful, but probably not well-known…

Using CASE in the ORDER BY Clause

As I said before, CASE can be used nearly anywhere in an SQL statement. A neat trick I use a lot is to strategically place a CASE in the ORDER BY clause of a SELECT statement to produce custom sorting of data.

Let’s say you were asked to display all the teachers, showing the Dark Masters at the top of the list and the Assistant Professors at the bottom. Sounds like a very convoluted request, but you might get something equally strange in your day-to-day work. How can CASE help us with this? Well, you can use CASE to attribute a “weight” to each record, thus making it show higher or lower on the output list: “lighter” records float to the top, “heavier” records drop to the bottom. Based on this piece of information, let’s write the statement that lists the teachers, showing Dark Masters at the top of the list and Assistant Professors at the bottom:

SELECT            NAME

            , RANK

FROM        UMADB_CHP4.TBL_TEACHERS

ORDER BY    CASE RANK

                  WHEN 'Dark Master' THEN 0

                  WHEN 'Assistant Professor' THEN 99

                  ELSE 1

            END

;

Here I’m assigning a weight of 0 to the records where the Rank is equal to “Dark Master” and a weight of 99 (any value greater than 1 would do, but 99 is “heavy” enough) to those records that have “Assistant Professor” on the Rank column. Everything ELSE gets a weight of 1. It’s another interesting and often overlooked way to use CASE, but it’s not the only way to produce customized sorting sequences.

Using a Secondary Table to Produce Customizable Sorting Sequences

Often, sorting sequences are natural to humans but completely undecipherable to machines. For instance, the American academic grading system seems simple enough, and SQL can handle it—that is, until you try to use the plus and minus following the grade. This happens because strings, or, in other words, everything that is not a number, are sorted character by character, with the code-page-defined sorting sequence, also known as the collating sequence. This sorting sequence doesn’t “know” that a grade of C is smaller than a grade of C+ and greater than a grade of C-. With just a handful of values, you can use a CASE expression to customize the sorting sequence, but this presents a huge disadvantage: you’re hard-coding the sort sequence, and any change to the data or an unforeseen value will disrupt the sorting and produce unexpected results. The solution seems simple: instead of hard-coding the sort sequence, let’s soft-code it.

For this, I’ll use a secondary table containing the possible values and their respective weights, and use it to sort. Let’s start by creating a generic table that can be easily reused:

CREATE TABLE            UMADB_CHP4.TBL_SORT_SEQ

   FOR SYSTEM NAME      PFSRTSEQ

   (

      TABLE_NAME FOR COLUMN SSTN CHAR(50)

      , COLUMN_NAME FOR COLUMN SSCN CHAR(50)

      , VALUE_NAME FOR COLUMN SSVN CHAR(100)

      , VALUE_WEIGHT FOR COLUMN SSVW INTEGER

      , CONSTRAINT UMADB_CHP4.PK_SORT_SEQ

            PRIMARY KEY(TABLE_NAME

                  , COLUMN_NAME

                  , VALUE_NAME

            )

   )

   RCDFMT PFSRTSEQR

;

A couple of notes about this table: I’m using a composite primary key, which guarantees the uniqueness of each TABLE_NAME, COLUMN_NAME, and VALUE_NAME combination. In other words, I’m making sure the sort sequence is unambiguous. Then notice that I’m providing short and long names, even though I don’t intend to use this table in RPG or another high-level programming language—at least, not now. It’s a good practice to think ahead, because in real life, it is usually not easy to replace a table definition. You can always use ALTER TABLE, but there are limitations. Always keep in mind that ALTER TABLE is not CHGPF, and SQL’s DDL is not the system native’s DDS! I could write pages and pages about this, but instead I advise you to read this very nice and concise article.

Now that I’ve created the table, let’s populate it with the numeric weights for the letter-based grade system. You can find those INSERT statements in the downloadable source code for this chapter. Take a moment to figure it out yourself (or just copy-paste the statements from the downloadable source code for this chapter), and then run the following statement:

SELECT            CLASS_NAME

            , CLASS_YEAR

            , STUDENT_NAME

            , GRADE

   FROM     UMADB_CHP4.TBL_GRADES

            INNER JOIN UMADB_CHP4.TBL_SORT_SEQ

                  ON TABLE_NAME = 'TBL_GRADES'

                              AND COLUMN_NAME = 'GRADE'

                              AND GRADE = VALUE_NAME

   WHERE    CLASS_NAME = 'Treachery'

ORDER BY    CLASS_NAME

            , CLASS_YEAR

            , VALUE_WEIGHT

;

I’m selecting a single class to show clear results. What you’ll see is that the A+ is displayed before the C grade, which is something that wouldn’t naturally happen because of the code-page character sequence. If you omit the WHERE clause, you’ll see a list sorted by class name, then by class year, and finally by letter-based grade! This is possible because each grade that matches the sort sequence table will return its weight, and the database engine will take this factor into account when performing the final sort of the output data.

Keeping in mind that this neat trick comes with a potential caveat: performance. Using a sort sequence in a JOIN clause may cause some performance issues, especially with large tables. Use it sparingly and always check for performance issues thoroughly before sending this sort of statement to a productive environment.

If you want a bit of practice, try with your data or use our sample database. For that, you can create the necessary records on TBL_SORT_SEQ for the teacher rank sequence, as shown in the table below, and then write a SELECT statement, similar to the one just shown, to list the teachers, sorted by their rank.

And this concludes this subseries. This subseries covered several different ways to make a database more user-friendly, sometimes in a slightly twisted way. Here’s a summary of what I’ve tried to explain here:

• How to recreate a table to include both short (program-friendly) and long (user-friendly) names
• How to implement complex record selection with the EXISTS predicate
• How to check database consistency with the EXCEPTION JOIN (SQL 101 – Making the DB User Friendly – Checking Database Consistency with the Exception Join)
• How to translate column values into something more “human-readable” with the CASE decision structure (SQL 101 – Making the DB User Friendly – Using CASE to Return “Friendlier” Column Values)
• How to use a secondary table to create customized sorting sequences (in this article)

Until next time, your comments, corrections, and suggestions are most welcome! Feel free to use the comments section below to reach out.

Rafael Victória-Pereira has more than 20 years of IBM i experience as a programmer, analyst, and manager. Over that period, he has been an active voice in the IBM i community, encouraging and helping programmers transition to ILE and free-format RPG. Rafael has written more than 100 technical articles about topics ranging from interfaces (the topic for his first book, Flexible Input, Dazzling Output with IBM i) to modern RPG and SQL in his popular RPG Academy and SQL 101 series on mcpressonline.com and in his books Evolve Your RPG Coding and SQL for IBM i: A Database Modernization Guide. Rafael writes in an easy-to-read, practical style that is highly popular with his audience of IBM technology professionals.

Rafael is the Deputy IT Director - Infrastructures and Services at the Luis Simões Group in Portugal. His areas of expertise include programming in the IBM i native languages (RPG, CL, and DB2 SQL) and in "modern" programming languages, such as Java, C#, and Python, as well as project management and consultancy.

---

MC Press books written by Rafael Victória-Pereira available now on the MC Press Bookstore.

		Evolve Your RPG Coding: Move from OPM to ILE...and Beyond Transition to modern RPG programming with this step-by-step guide through ILE and free-format RPG, SQL, and modernization techniques. List Price $79.95 Now On Sale
		Flexible Input, Dazzling Output with IBM i Uncover easier, more flexible ways to get data into your system, plus some methods for exporting and presenting the vital business data it contains. List Price $79.95 Now On Sale
		SQL for IBM i: A Database Modernization Guide Learn how to use SQL’s capabilities to modernize and enhance your IBM i database. List Price $79.95 Now On Sale