Ted Holt
-
Guru: Another Red Flag – Joining On Expressions
August 30, 2021 Ted Holt
One would think that a given datum, for example a sales order number, would be defined identically in the various database tables in which it is found within an organization, but one might be wrong. I have on many occasions faced the challenge of joining two or more tables on unmatched data types.
But it gets worse than that. Sometimes joins involve expressions, which may consist of mathematical operations and/or invocations of functions, both intrinsic and user-written. As with the word DISTINCT in a SELECT, the presence of expression in a join sends up a little red flag that …
Read more -
Guru: Set Beats A Loop
August 23, 2021 Ted Holt
Hey, Ted:
I’m sending you some RPG source code that I put into one of our inquiry programs and would like to have your insight. Instead of using the SETLL, DOW and READE opcodes to loop through a group of selected records, I used the SQL SET statement. This seems to me much easier for someone else to understand and follow. Is this good practice?
— Mike
I was glad to see Mike’s email in my inbox, as always. He and I met in person at the RPG & DB2 Summit a few years ago, and I’ve enjoyed getting to …
Read more -
Guru: Odds and Ends
August 16, 2021 Ted Holt
I really appreciate the comments that readers add to the end of articles or send to me in email. The more you share, the more all of us are better off. I know that you don’t have time to go back through articles we have published to see if anyone has commented, so this week I do that for you. Please keep the feedback coming!
Several people posted responses to Paul Tuohy’s article Getting Meaningful Audit Information from a Journal. You can read them for yourself, but I wanted to thank Emanuele, who mentioned a tool that is similar …
Read more -
Guru: The Deception of Fractional Labeled Durations
August 9, 2021 Ted Holt
Hey, Ted:
We measure certain processes in tenths of an hour. For whatever reason, we cannot make date/time arithmetic work properly when dealing with this data. There must be something that we don’t understand — what we’re doing seems simple and straightforward. Can you help?
— One Confused Reader
What this reader wants to do makes perfect sense. He wants to take a value like 8:00 AM, add a fractional number of hours to it, let’s say 1.1, and come up with 9:06 AM. Let’s try an example.
declare global temporary table StampData ( Stamp timestamp ); insert into session.StampData… Read more -
Guru: Elapsed Time For Human Beings
August 2, 2021 Ted Holt
Quick! Think of a weird animal! Perhaps you thought of the platypus, a duck-billed mammal that lays eggs. Maybe Dr. Doolittle’s pushmi-pullyu jumped to mind. Maybe you thought of a politician or a musician or your next-door neighbor. I know a weird animal that you probably didn’t think of.
The weird animal I have in mind is called the duration. This animal is found in SQL queries. There are three species: timestamp, date, and time. Today I write about the challenges of interacting with this strange entity. I’ll use the timestamp duration as an example. Interacting with date …
Read more -
Guru: One-Shot Requests and Quoted Column Names
July 19, 2021 Ted Holt
If I had a dollar for every time someone has asked me to query data for them over the years, I could have retired already. (I did not say I would have, but that I could have.) It’s nice when the users can retrieve the information they need to do their jobs without help from IT, but when a request is too complex for them, I’m always glad to help.
I’ve used many tools over the years for one-shot requests for raw data. These days my tool of choice is the Run SQL Scripts tool, which is part of IBM …
Read more -
Guru: Use SQL To Replace Reports
June 14, 2021 Ted Holt
Does anybody write report programs anymore? If so, why? Reports are so twentieth-century. The people we serve want spreadsheets, Web pages, cell-phone apps and other modern conveniences. What we need is an easy way to make our report programs write the same information to modern interfaces without having to perform major surgery on them.
Some 10 years or so ago, I built a scripting language to enable me to reformat spooled files into stream files in CSV format. I called my utility BASS. It was, and continues to be, a successful project.
But BASS is not the only way …
Read more -
Guru: Date Format Confusion
May 10, 2021 Ted Holt
Hey, Ted:
I use the Copy to Import File (CPYTOIMPF) command to build a CSV file to be sent to a MySQL system. My colleagues are unable to import the file because of the format of a timestamp field. CPYTOIMPF puts a hyphen between the date and time (e.g. 2021-05-10-04.36.24.849555), but the other system wants a space between them. Other than writing a custom program do you know of another way to change the format of the timestamp output?
— Richard
You won’t need a custom program, Richard. There’s another way to handle this requirement.
IBM has already very thoughtfully …
Read more -
Guru: Compare Pieces Of Source Members
April 19, 2021 Ted Holt
Next to Barbara Morris, the F15 key seems to be the RPG programmer’s best friend. I do not like duplicated source code, but for years I have been finding it everywhere. Sometimes I find the same source code in multiple source members. Sometimes I find the same source code two or more times in a single source member. If you’ve been programming in an IBM i shop for more than two hours, you know what I’m talking about.
As I wrote three years ago in this august publication, duplicated source code is bad because it embodies the WET principle, …
Read more -
Guru: Stub Testing And SQL
March 29, 2021 Ted Holt
A stub program is a program that does nothing but stand in as a place holder for a real program, which may or may not exist yet. I have used them for years to help me test program changes. There are also stub subroutines, stub subprocedures, etc. What I want to talk about today is how to use a similar concept for SQL queries.
You can use your favorite search engine to learn about stubs, but I’ll give you an example to increase the chances that you’ll know what I’m talking about. Let’s say that I am modifying a CL …
Read more