It’s time to tackle another nagging issue, this time directly from the end user front: cryptic values in columns, which programs understand and handle – but users… Not so much.

Written by Rafael Victoria-Pereira

A common complaint from the users is that the data they see in DB2 for i queries doesn’t match what they have in the program’s output. For instance, a program may read a 1 from the database and show “Active” on the screen, or translate “*RDY” to “Ready for delivery”. Typically, programs do this sort of data translation using If-Else or Case (Select-Case in RPG) decision structures. To obtain similar results when querying the IBM i’s database, similar functionality is required.

SQL provides a CASE structure, similar to many other languages. SQL’s version acts at the column level, which means you can use it nearly anywhere in an SQL statement (SELECT, ORDER BY, HAVING clauses, just to name a few examples). Let’s start with a very simple example and build on it to create a useful overview of the Grades table.

If you’re American, or familiar with the American academic grading system, you know what a final grade of F means: bad news. This translation is automatic, imbued in your brain after many years of academic life. That’s basically the reason why I chose it for this example. Let’s take the Grades table and build a query over it that provides all relevant data (student, class, and grade) in a user-friendly way, hiding the database complexity from the user. To begin with, let’s show the student’s name, class name, and class year, along with the grade achieved:

SELECT            STUDENT_NAME

            , CLASS_NAME

            , CLASS_YEAR

            , GRADE

FROM        UMADB_CHP4.TBL_GRADES;

The grade is recognizable, but a simpler classification, such as “PASSED” or “FAILED,” would be better. We’ll do that translation with the CASE expression, but before showing the modified Select statement, let me refresh the CASE syntax:

CASE <expression>

WHEN <value to match> THEN <value to return>

...

ELSE <value to return when a match is not found>

END

Here <expression> is a column name or some expression based upon, such as a SUBSTR or another scalar function’s result. After this first line, one or more WHEN conditions may follow. If a condition is met, a matching value is found, and when the <value to return> is output instead of the initial expression. If the optional clause ELSE is specified and none of the WHEN statements match the value produced by the expression on the first line of the CASE, then the <value to return when a match is not found> value is output. This will become clearer in a moment, with an example. Before that, let me just show you an alternative syntax:

CASE

WHEN <condition> THEN <value to return>

...

ELSE <value to return when a match is not found>

END

In this syntax, the WHEN lines hold both the expression and the value to match, forming a condition. The nice thing about this syntax is that it provides additional flexibility. However, once the first condition is met, the corresponding value is returned, and the check stops. We’re finally ready to add the CASE to the SELECT statement I showed a while ago:

SELECT      STUDENT_NAME

            , CLASS_NAME

            , CLASS_YEAR

            , CASE GRADE

                  WHEN 'F' THEN 'FAILED'

                  ELSE 'PASSED'

            END AS FINAL_GRADE

FROM        UMADB_CHP4.TBL_GRADES;

This is comparable to an IF statement in most programming languages. To demonstrate the alternate syntax of CASE, let’s rewrite this query to look even more like an IF statement:

SELECT      STUDENT_NAME

            , CLASS_NAME

            , CLASS_YEAR

            , CASE

                  WHEN GRADE = 'F' THEN 'FAILED'

                  ELSE 'PASSED'

            END AS FINAL_GRADE

FROM        UMADB_CHP4.TBL_GRADES;

Notice how I omitted the expression after the CASE keyword and used a condition on the WHEN line instead. This provides additional flexibility and can be useful when you’re transferring business rules from a high-level programming language to SQL. This is one of the neat tricks I’ll use later on this series to “beef up” the database. For now, let’s go back to our Grades example and upgrade it to unleash the full power of CASE with a more detailed classification of the final grade:

SELECT      STUDENT_NAME

            , CLASS_NAME

            , CLASS_YEAR

            , CASE GRADE

                  WHEN 'F' THEN 'FAILED'

                  WHEN 'D' THEN 'BARELY MADE IT'

                  WHEN 'C' THEN 'AVERAGE RESULT'

                  WHEN 'A' THEN 'EXCELLENT SCORE'

                  WHEN 'A+' THEN 'MASTER!'

                  ELSE 'PASSED'

            END AS FINAL_GRADE

FROM UMADB_CHP4.TBL_GRADES;

In this example, I’m using multiple WHEN lines to produce fine-grained results and a “catch-all” clause, with the final ELSE line. As you can see from these statements, CASE is a very useful tool for translating data, making it more user-friendly, but it can do much more. That’s what the next article is going to be all about!

It’s time to tackle another nagging issue, this time directly from the end user front: cryptic values in columns, which programs understand and handle – but users… Not so much.

Written by Rafael Victoria-Pereira

A common complaint from the users is that the data they see in DB2 for i queries doesn’t match what they have in the program’s output. For instance, a program may read a 1 from the database and show “Active” on the screen, or translate “*RDY” to “Ready for delivery”. Typically, programs do this sort of data translation using If-Else or Case (Select-Case in RPG) decision structures. To obtain similar results when querying the IBM i’s database, similar functionality is required.

SQL provides a CASE structure, similar to many other languages. SQL’s version acts at the column level, which means you can use it nearly anywhere in an SQL statement (SELECT, ORDER BY, HAVING clauses, just to name a few examples). Let’s start with a very simple example and build on it to create a useful overview of the Grades table.

If you’re American, or familiar with the American academic grading system, you know what a final grade of F means: bad news. This translation is automatic, imbued in your brain after many years of academic life. That’s basically the reason why I chose it for this example. Let’s take the Grades table and build a query over it that provides all relevant data (student, class, and grade) in a user-friendly way, hiding the database complexity from the user. To begin with, let’s show the student’s name, class name, and class year, along with the grade achieved:

SELECT            STUDENT_NAME

            , CLASS_NAME

            , CLASS_YEAR

            , GRADE

FROM        UMADB_CHP4.TBL_GRADES;

The grade is recognizable, but a simpler classification, such as “PASSED” or “FAILED,” would be better. We’ll do that translation with the CASE expression, but before showing the modified Select statement, let me refresh the CASE syntax:

CASE <expression>

WHEN <value to match> THEN <value to return>

...

ELSE <value to return when a match is not found>

END

Here <expression> is a column name or some expression based upon, such as a SUBSTR or another scalar function’s result. After this first line, one or more WHEN conditions may follow. If a condition is met, a matching value is found, and when the <value to return> is output instead of the initial expression. If the optional clause ELSE is specified and none of the WHEN statements match the value produced by the expression on the first line of the CASE, then the <value to return when a match is not found> value is output. This will become clearer in a moment, with an example. Before that, let me just show you an alternative syntax:

CASE

WHEN <condition> THEN <value to return>

...

ELSE <value to return when a match is not found>

END

In this syntax, the WHEN lines hold both the expression and the value to match, forming a condition. The nice thing about this syntax is that it provides additional flexibility. However, once the first condition is met, the corresponding value is returned, and the check stops. We’re finally ready to add the CASE to the SELECT statement I showed a while ago:

SELECT      STUDENT_NAME

            , CLASS_NAME

            , CLASS_YEAR

            , CASE GRADE

                  WHEN 'F' THEN 'FAILED'

                  ELSE 'PASSED'

            END AS FINAL_GRADE

FROM        UMADB_CHP4.TBL_GRADES;

This is comparable to an IF statement in most programming languages. To demonstrate the alternate syntax of CASE, let’s rewrite this query to look even more like an IF statement:

SELECT      STUDENT_NAME

            , CLASS_NAME

            , CLASS_YEAR

            , CASE

                  WHEN GRADE = 'F' THEN 'FAILED'

                  ELSE 'PASSED'

            END AS FINAL_GRADE

FROM        UMADB_CHP4.TBL_GRADES;

Notice how I omitted the expression after the CASE keyword and used a condition on the WHEN line instead. This provides additional flexibility and can be useful when you’re transferring business rules from a high-level programming language to SQL. This is one of the neat tricks I’ll use later on this series to “beef up” the database. For now, let’s go back to our Grades example and upgrade it to unleash the full power of CASE with a more detailed classification of the final grade:

SELECT      STUDENT_NAME

            , CLASS_NAME

            , CLASS_YEAR

            , CASE GRADE

                  WHEN 'F' THEN 'FAILED'

                  WHEN 'D' THEN 'BARELY MADE IT'

                  WHEN 'C' THEN 'AVERAGE RESULT'

                  WHEN 'A' THEN 'EXCELLENT SCORE'

                  WHEN 'A+' THEN 'MASTER!'

                  ELSE 'PASSED'

            END AS FINAL_GRADE

FROM UMADB_CHP4.TBL_GRADES;

In this example, I’m using multiple WHEN lines to produce fine-grained results and a “catch-all” clause, with the final ELSE line. As you can see from these statements, CASE is a very useful tool for translating data, making it more user-friendly, but it can do much more. That’s what the next article is going to be all about!

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