Ted Holt is the senior technical editor at The Four Hundred and editor of the former Four Hundred Guru newsletter at Guild Companies. Holt is Senior Software Developer with Profound Logic, a maker of application development tools for the IBM i platform, and contributes to the development of new and existing products with a team that includes fellow IBM i luminaries Scott Klement and Brian May. In addition to developing products, Holt supports Profound Logic with customer training and technical documentation.
May 15, 2017 Ted Holt
In Three Ways To Manage Unmatched Data I wrote about the use of the RAISE_ERROR function to force a SELECT statement to cancel when unmatched data is considered a fatal error. Another good use of RAISE_ERROR is to force an UPDATE statement to cancel when an invalid condition occurs.
To illustrate, imagine that you and I work in a factory. All factories have inventory. The people we serve purchase some inventory items and manufacture others. Our job is to write a program that will allow certain people to zero out the inventory balance for certain types of purchased items.
The …Read more
May 8, 2017 Ted Holt
The INSERT statement is THE (as in the only) SQL way to add new data to a relational database table. At the risk of sounding like a GEICO commercial, “Everybody knows that.” Well, did you know that the INSERT statement supports three distinct ways to add new rows to a table?
To illustrate the three forms of INSERT, imagine that you and I work for a small company that stores goods in, and ships goods from, a warehouse. Since the company has only one warehouse, there has never been a need for a warehouse ID column in any of …Read more
May 1, 2017 Ted Holt
We IBM i developers owe a great debt to Scott Forstie. He’s responsible for the wonderful DB2 for i Services and IBM i Services, which give us SQL interfaces for many functions of the operating system. Like IBM, we can write SQL interfaces to help us with non-database tasks. I recently did exactly that.
While I can’t say that I never use the Start SQL Interactive Session (STRSQL) command, I can say that I prefer to use GUI SQL clients, in particular the Run SQL Scripts utility that is part of IBM i Access Client Solutions (ACS). I do get …Read more
April 24, 2017 Ted Holt
Heaven forbid that I would ignore a failed RPG CHAIN (random read) operation. I always take appropriate action. Which action I take depends on the situation. The same applies to outer joins that don’t find matching data in a secondary table. Here are three ways to deal with unmatched data in an outer join using SQL.
To illustrate, let’s use three tables from an overly simplified general ledger system. The first is a table of departments into which the business is divided. The second is a chart of accounts. The third is a transaction file that feeds the general ledger. …Read more
April 17, 2017 Ted Holt
In last week’s tip, you mentioned that expressions are not allowed in the ORDER BY clause of a union. You can use the union as a subquery to allow the use of an expression for the ordering. An extra layer, but it gives you the result you are looking for.
Sims is correct. I was so focused on the fact that the ORDER BY of a union does not allow expressions that I completely forgot about a workaround. His (her?) technique is probably the most common way to deal with this limitation. I’ve seen numerous examples …Read more
April 10, 2017 Ted Holt
UNION and ORDER BY are powerful SQL features, but put the two together and you may get some strange and frustrating error messages. Fortunately for us DB2 for i professionals, there are easy ways to make the two collaborate and cooperate. Today is a great day to be sure we understand them.
We need some data for examples. For some of the queries, I use a customer master table (file) and a vendor master table. These are a company’s trading partners, so the two have many attributes in common, especially names and addresses. I also use two sales history tables, …Read more
March 27, 2017 Ted Holt
I hope that whoever came up with the idea for the Copy to Import File (CPYTOIMPF) command was well compensated. When I think of the time and effort that that command has saved me and countless others, I feel deep gratitude. The addition of the ORDERBY parameter increased the usefulness of CPYTOIMPF, and I’d like to share that with you.
CPYTOIMPF copies a single-format database file (table, physical file, view, or logical file) to a stream file or physical file in a format that is acceptable to another system or application. Probably the most common use of this command is …Read more
March 20, 2017 Ted Holt
Bob writes, “Hey, Ted! I hope you can teach an old dog a new trick. I am trying to replace the CHAIN operation with SQL. I chain once to a file to read a certain record. If that record is not found, I chain again to retrieve a default record. How can I make SQL do a second read to the same file?”
This is not a hard thing to do, as SQL has no problem joining more than once to the same table. I’ll show you two methods to retrieve your data. The first method is the easier one, …Read more
March 6, 2017 Chris Ringer
If you read my two previous PHP articles, you may be tempted to make the leap to use PHP on your IBM i. But you may also have reservations because how in the world will you technically support those apps post implementation?
This article discusses the path a PHP request takes as it travels through various subsystems up to your RPG code and how to do basic troubleshooting if something goes wrong.
Tag Team Match
Often greatness is achieved with the help of someone else. Michael Jordan had Scottie Pippen. Babe Ruth had Lou Gehrig. And Abbott had Costello. In …Read more
February 27, 2017 Ted Holt
I was annoyed that IBM chose not to support GOTO and TAG in free-form RPG calculations. I rarely used those opcodes, but when I did, it was in disciplined situations when nothing else would do. Besides, I didn’t want to be treated like a baby. Now it’s a non-issue. IBM finally provided an alternative to the last legitimate use of GOTO.
Almost five years ago, I wrote about a situation that I come across from time to time, namely the need to execute a clean-up routine before exiting an RPG subprocedure. Since nothing forbids a subprocedure to have multiple …Read more