Notes

LTRIM, RTRIM and Trim Expressions

I have to admit that, when trimming strings with SQL, I tend to just use the TRIM function to strip off all leading and trailing spaces. But the LTRIM and RTRIM functions really do deserve more attention because they have been recently enhanced to support trim expressions. That is, all Read more…

By Paul, ago
Notes

Handling divide by zero in SQL

Division by zero is something that, sooner or later, we all have to capture. For the rest of this post, I am using a small temporary table that I have imaginatively named DIV0. It has two columns, COLUMN1 and COLUMN2 and it looks like this: COLUMN1 COLUMN2 5 0 6 Read more…

By Paul, ago
Notes

Combining Counts for multiple tables

It’s surprising how many times you need to know whether a record exists in any one of two or more tables. Traditionally, you would have to check each table individually until you either find a match or have checked every table to confirm that no match exists. Finding myself faced Read more…

By Paul, ago
Notes

Using the SQL Translate Function

The SQL TRANSLATE function is worth knowing about if you ever find yourself needing to (for example) fix data from an external feed before you attempt to do anything with it. The function requires returns a value in which one or more characters in expression have been converted into other Read more…

By Paul, ago
Notes

Interpreting the DSPPGMREF file usage

I mentioned the DSPPGMREF command a couple of weeks ago. This command allows you to generate a list of system objects used by specified programs and is particularly handy when you want to see which programs are doing what to a specific file. The only quirk is that the file Read more…

By Paul, ago
Notes

Read and Update with SQL

One of the advantages of SQL over native IO is that it allows you to handle sets of data rather than having to read and update each row individually. However, there can be cases where you want to handle your data line by line. To do this with SQL, you Read more…

By Paul, ago
Notes

Using regular expressions in DB2 for i

Regular expressions have been supported in DB2 for a while now and, while they are not something I often need, there are times when they come in very handy indeed. A simple example involves cleaning up some data so that it can be exported from an old (poorly maintained) database Read more…

By Paul, ago
Notes

Merging data with SQL MERGE

There have been times, many times, when I have needed to update the one or more columns in a table with values retrieved from another. A traditional approach to this is to use an UPDATE with subselects but there is a better way: The MERGE statement updates a target (a Read more…

By Paul, ago
Notes

Better date conversion with timestamp_format

Handling dates and times on the IBM i can often be a bit of a challenge. Obviously, if you are working with a modern application that uses the Date, Time and Timestamp data types everything is nice and straightforward. However, there are plenty of old applications that use eight, seven Read more…

By Paul, ago
Notes

Adding variables to ad-hoc SQL queries with CL

Last month I mentioned using REXX to build and execute ad-hoc SQL queries. For the sake of completeness, here is the same solution implemented in CL using the RUNSQL command. The program does exactly the same as the previously describe REXX script — extracts all records flagged with today’s date Read more…

By Paul, ago