How to Count with SQL

October 26, 2005 Ted Holt

SQL can count two ways–vertically and horizontally. Anybody who uses SQL for data retrieval should know both methods. If you use SQL and don’t know how to count both ways, you’re in luck, because I’m about to explain both techniques.

Let’s consider vertical counting first because it’s the easier method. To count vertically, use the COUNT function and the GROUP BY clause. To illustrate, I’ll count records in my favorite database file QIWS/QCUSTCDT. You can find this file on your system, but to save you the trouble, here’s the data we’ll be using.

LSTNAM    INIT  STATE  CHGCOD
Henning   G K    

Those Stupid Quotation Marks!

October 26, 2005 Hey, Ted

I am attempting to use QCMDEXC to run Open Query File within an RPG IV program but the compiler generates errors RNF0312 (A right parenthesis is expected but is not found) and RNF5347 (An assignment operator is expected with the EVAL operation). Do you see anything wrong with my code?

–PG

Here’s the free-format RPG code PG sent me. (The file and field names have been changed to protect the guilty.)

@qcmdexec('OPNQRYF FILE((MYFILE)) QRYSLT('FLD *EQ %WLDCRD("*AA*")')');

PG is trying to select all records in file MYFILE that have AA anywhere in field FLD. PG’s problem is that he (she?) has

Admin Alert: New TCP/IP Functions to Check Out When Upgrading to i5/OS V5

October 26, 2005 Joe Hertvik

As shops upgrade old AS/400 and iSeries systems to modern i5 machines running i5/OS V5R3, many administrators are discovering their newer operating systems provide more goodies than the systems they left behind. This week, I reviewed some of the newer TCP/IP features that IBM added to the system starting in i5/OS V5R1. If you are upgrading your old AS/400 gear this year, here are some of the new TCP/IP features you may now be able to take advantage of.

1. Checking your i5/OS TCP/IP routing by using TRACEROUTE. Long available in most other networked operating systems, Trace TCP/IP Route (TRACEROUTE) only

Correction: Bugged by the Bugged by Interactive Debugger Tip

October 26, 2005 Brian Kelly and Joey Maniskas

Editor’s note: Last week, Brian and Joey took a question from Dave regarding the SQL interactive debugger and from the responses we received to the tip, we created as much confusion as we attempted to address. The fact is we got our commands mixed up in the solution. Our sincere apologies for the inconvenience this may have caused anybody out there in iSeries-land. The moral of the story of course is check, double check, recheck, and then check again. Here is the original question followed by two emails from some sharp early bird readers of Four