Ted Holt
-
Guru: A Simple, Effective Way To Compare Data Using SQL
April 20, 2020 Ted Holt
One thing I have learned over the years is that no matter how much technology changes, people always ask the same questions like: “Why does the computer say we have 50 widgets when we only have 45?” and “Can you tell me who changed the due date of order number 12345?” and “How do sales of widgets this year compare to the same time last year?” It is the last of these that I wish to address today.
Comparing data is a very common activity. Everybody compares data: sales people, production people, purchasing people, and of course management people. This …
Read more -
Guru: SQL Checks For Control Breaks
April 6, 2020 Ted Holt
A control break occurs when the combined value of one or more fields changes from one row (record) to the next when reading a data set sequentially. I used to write RPG programs with control breaks often. Now that reports are less common, I write them less often, but that’s not to say I never write a program with control breaks.
When I first learned to handle control breaks in RPG, I used the L1 through L9 level indicators. These worked wonderfully and fed my family for several years. When I moved from the System/36 world to the S/38 (and …
Read more -
Guru: SQL Can Generate A Series
March 16, 2020 Ted Holt
Most of my work is run-of-the-mill, same-old-same-old, more-of-the-same. I’m not complaining. Most of life is mundane and routine, and that’s as it should be. Too much icing ruins the cake. But sometimes I get a challenge, and when that happens, programming can be fun.
Today’s article comes out of such an experience. I needed a table with a week’s worth of dates in it. I could have written an RPG program, but I knew that SQL could handle the task. Today I’ll show you a couple of methods that you can use to generate a series of whole numbers and …
Read more -
Guru: In Pursuit Of Reasonable Data
March 9, 2020 Ted Holt
My awareness of the indifference of computers toward the reasonableness of data dawned when I enrolled in the university. The band director told me to sign up for band and jazz band for zero semester hours. He said the stupid computer wouldn’t know that no one takes a course for zero hours, wouldn’t flag me to obtain permission from the dean to carry an overload, yet would grant me the credit. He was right.
Love ‘em or hate ‘em, we live with them, we learn to get around them when they limit us, and we deal with them when they …
Read more -
Guru: Alternate SQL Row-Selection Criteria Revisited
February 24, 2020 Ted Holt
At the RPG & DB2 Summit last October, I chanced to overhear Rob Bestgen of IBM telling Paul Tuohy of System i Developer about a practice that he and others in IBM Lab Services had seen, a practice which they found horrifying. “Oh, no!” I thought. “I’ve written about that for itjungle.com!” Are you ready to be horrified?
Rob was telling Paul that they have seen SQL queries in which portions of the WHERE clause are enabled or disabled through the use of switches. I did not invent this technique — I realized more than a few years ago that …
Read more -
Guru: SQL Functions Can Do Non-function Things
January 27, 2020 Ted Holt
We all know that the SQL SELECT statement only retrieves data, right? If you want to modify data, you have to use INSERT, UPDATE, DELETE, or MERGE, correct? Well, to quote the great George Gershwin, It Ain’t Necessarily So. You can modify data from a SELECT statement, and maybe sometimes you should. Here’s how it’s done.
I’ll illustrate with a SELECT statement that will run the Reorganize Physical File (RGZPFM) command over physical files that have at least 10 percent deleted records. I could do this with plain ol’ CL, of course, and that’s probably the approach I would …
Read more -
Guru 2020: Suggested Resolutions, One Prediction
January 6, 2020 Ted Holt
Do you eat candy made of underwear? How are your telepathy and teleportation skills? How long ago did you give up eating? How many choppers are on the family helipad? Is your chauffeur a gorilla? Read about these and other bizarre predictions for 2020 here.
A new year is invariably accompanied by resolutions and predictions. I don’t intend to make any of either. However, if you’re into making resolutions, I’ve got some suggestions that you can take or leave, as you wish. As for predictions, I’ve got one that can’t miss.
For RPG programmers who are still looking for …
Read more -
Guru: More End Of Year Feedback
December 9, 2019 Ted Holt
You are busy. The people you serve need you to do more than one human being can do. You don’t have time to look for comments or updates to the articles we run in this august publication or any other. For this reason, I was pleased to publish some of your feedback in last week’s issue. This week I am pleased to share a bit more.
In response to Guru: MERGE, Chicken, and Eggs, John asked a good question and made a good point:
How is using this merge technique under commitment control any different than just doing the …
Read more -
Guru: End Of Year Feedback
December 2, 2019 Ted Holt
The year has flown by. Before we know it, it will be 2020. The century is flying by, too. We’ve almost consumed a fifth of it. That seems like a good excuse to see what we might glean from some of your feedback. It’s been a while. More to come next week!
Several readers wrote regarding the need to remove hard-coded values from programs. Jim brought up the problem of compile-time tables and arrays.
I find cases where data is hard coded (state names, product categories are a few examples) for tables or arrays in dozens of programs.
I wish …
Read more -
Guru: Better Check Constraints
November 11, 2019 Ted Holt
This article has three purposes. If you use check constraints in your database, the purpose is to help you make better use of check constraints. If you don’t use check constraints, the purpose is to encourage you to use them and to point you in the right direction. If you already know all this stuff, the purpose is to goad you to email me and teach me something I don’t know.
The purpose of check constraints is to keep invalid data out of the database. That may seem unnecessary. Isn’t that what the applications are supposed to do? Yes, but …
Read more