Changing Case using SQL

Last week I discussed how to change the case of alphabetic characters using the new %UPPER and %LOWER BIFs, built in functions, added to CL in IBM i 7.2, you can read it here. I mentioned the limitations of using the %XLATE BIF in RPG to accomplish the same. But there is an easier way using embedded SQL in the RPG to emulate the functionality of CL's %UPPER and %LOWER do.

RPG's %XLATE requires that you need to give all of the characters that should be replaced with what you need them to be replaced by. This is useful for so many situations. But when converting case most programs I have seen many that just have the following:

IBM i facts to surprise you CIO

 

The web page that was originally linked to no longer exists, therefore, rendering this page obsolete.

 

CL Built in Functions to Change Case

IBM i 7.2 brought us two new CL built in functions (BIFs), %UPPER and %LOWER, that are used translate the contents of a variable or string to upper or lower case. I have always used the RPG BIF %XLATE to achieve the same result, I just wish IBM had added the same BIFs to RPG.

The syntax for both of these CL BIFs is similar to each other with just two parameters:

1. Variable or string to convert - mandatory
2. CCSID - optional

For example:

Reading a SQL table in SQL

In a previous post I explained how to handle a SQL table using RPG operation codes to perform the file access. The post was just to illustrate that it was possible. I received messages expressing surprise that I had chosen to use RPG and not SQL. I think those who sent me those messages missed the point of the post. Anyway in this post I am going to show the same example, but using SQL for accessing the SQL table.

If I was doing this myself I would probably replace the entire DO loop in the RPG program with the following SQL statement:

Finding triggers using SYSTRIGGER

Triggers allow business logic to be placed at the file/table level, independent of the software. When you access the file/table with a program, SQL, DFU, or DBU the trigger will execute. Before making changes to objects or creating test data it is important to find what triggers are present on which files and tables to stop them from executing in unexpected manners.

Fortunately there is a SQL view, SYSTRIGGER in QSYS2, that contains all the information we need to know about all the triggers on the IBM i. I am not going to list all the columns in SYSTRIGGER as IBM's documentation job does a good job of doing it here.