Ted Holt
-
Guru: SQL Facts Of UNION And ORDER BY, Take Two
April 17, 2017 Ted Holt
Hey, Ted:
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.
–S. Sims
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 -
Guru: SQL Facts Of UNION And ORDER BY
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 -
Guru: Sorting Options For CPYTOIMPF
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 -
Conditional SQL I/O, Take 2
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 -
Guru: The PHP Path To Victory, Part 1
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 -
Guru: Eliminate The Legitimate Use Of GOTO, Take Two
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 -
Guru: SQL PL Labels
February 13, 2017 Ted Holt
SQL PL, the powerful, easy-to-learn procedural database language that IBM ships with all versions of DB2, allows you to identify any executable statement or compound statement with a label. I can’t think of a better day than today for us to think about why we might want to do that.
As a minimalist programmer, I avoid useless code, and that includes unnecessary labels. In the following paragraphs, I will list only the cases that require labels.
But first, a few ground rules.
- As with CL, a label name is followed by a colon when used to identify a statement.
-
Guru: SQL PL, WHILE And REPEAT Loops
January 18, 2017 Ted Holt
In earlier editions of this august publication, I covered the SQL PL looping structures that I consider to be the most useful. Today I cover the remaining looping structures. I cover them for completeness, but also because what I consider to be most useful may not be what you consider to be most useful.
To review, the FOR loop provides a simple way to process the rows of a result set. LOOP structure is versatile, providing the freedom to exit a loop from any point. The remaining loop structures are WHILE and REPEAT.
The WHILE loop is a top-tested loop. …
Read more -
RPG And The BLOB
December 13, 2016 Hey, Mike
… Read moreI’m trying to use a SELECT/INTO embedded SQL statement in an RPG program that accesses a table that includes a BLOB(20K) column. The INTO target is an externally defined data structure based on the table. However, I get compiler errors unless I remove the BLOB column from the table. What’s up with that?
—Four Hundred Guru reader
The problem is that RPG doesn’t have a native data type equivalent of a BLOB (or the other large object types CLOB/DBCLOB, for that matter.) The reason for this is that large object types can be up to 2GB in size, far
-
QTEMP Is A Different Animal
November 15, 2016 Hey, Ted
… Read moreI have a problem with an FTP script. I wish to send a file in QTEMP to a remote system, but the script tries to send the file from QGPL instead. The PUT command does not tell which library to send the file from because our standards prohibit the use of hard-coded library names. Any thoughts will be appreciated.
–Al
I commend you and other wise people in your shop for outlawing the hard-coding of library names, Al. The library list is a wonderful feature of IBM i, a feature that is sorely missing from other database systems. I have