Guru: Set Loop Rhythms
23 Aug 2021
I am sending you some RPG source code that I put into one of our survey programs and I would love to hear from you. Instead of using the SETLL, DOW, and READE opcodes to iterate through a group of selected records, I used the SQL SET statement. It seems to me a lot easier for someone else to understand and follow. Is this a good practice?
I was happy 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 enjoyed getting to know him. I was delighted to see it move away from record level access and move towards SQL. I get a good number of emails from readers who want to use SQL in their programs, but have encountered some kind of problem. Maybe it’s finally time I broached this subject.
First of all, we need a small database table for illustration.
create table Sales ( Order dec(5) not null, Line dec(3) not null, Deleted char(1) not null with default, Qty dec(3) not null with default, Price dec(5,2) not null with default, primary key (Order, Line) );
Now let’s see what kind of code Mike said he didn’t want to write anymore.
FSales if e k disk rename(Sales: SalesRec) D ctOrderLines s 3p 0 D sumOrderLines s 7p 2 D SearchOrder s 5p 0 C EVAL ctOrderLines = *zero C EVAL sumOrderLines = *zero C SearchOrder SETLL Sales C SearchOrder READE SalesRec C DOW not %eof(Sales) C IF Deleted <> 'Y' C EVAL ctOrderLines += 1 C EVAL sumOrderLines += (Qty * Price) C ENDIF C SearchOrder READE SalesRec C ENDDO
And that’s the kind of source code Mike sent us after we did a bit of work on it.
exec sql set (:ctOrderLines, :sumOrderLines) = (select count(*), sum(Qty * Price) from Sales where Order = :SearchOrder and Deleted <> 'Y');
Do these accomplish the same thing? Not enough. If there is at least one active (i.e. not deleted) row for the desired command, yes, both give the same results. But if there are no active rows, the second column returns null. (But not the first column. The count function never returns null.) This was easily fixed with the coalesce function.
exec sql set (:ctOrderLines, :sumOrderLines) = (select count(*), coalesce(sum(Qty * Price), 0) from Sales where Order = :SearchOrder and Deleted <> 'Y');
Now they are equivalent. Wasn’t it easy?
Something else puzzled me. Mike had used the SET instruction. Out of habit, hardened by years of integrating SQL into my RPG programs, I would have used SELECT INTO.
exec sql select count(*), coalesce(sum(Qty * Price), 0) into :ctOrderLines, :sumOrderLines from Sales where Order = :SearchOrder and Deleted <> 'Y';
This made me wonder if there was a difference between SET and SELECT INTO. I found a slight difference in the 7.4 documentation. (See the links for SET and SELECT INTO in the related stories.) Here it is:
[SET] can be integrated into an application program. This is an executable statement that can be dynamically prepared if all of the defined variables are global variables. It should not be specified in REXX.
[SELECT INTO] can only be integrated into an application program. This is an executable statement that cannot be prepared dynamically. It should not be specified in REXX.
Not much, is it? I wrote a few short test programs and SET and SELECT INTO behaved identically.
The answer to Mike’s question is a YES! It is certainly good practice.
I find it hard to believe that so many stores still don’t use SQL in their programs, but over the past few years I’ve seen enough to convince myself that the code currently written in many stores looks like what I used to write in the 1990s. Hearing people like Mike encourages me and gives me hope that the future of this robust IBM i platform is not as bleak as the trade press says.
If you’re having trouble with SQL, send me your code and I’ll try to help you. Most of the people who email me with SQL issues are close to the solution and it’s not hard to put them on the right track. I won’t make any promises, but I will help if I can. This is not a completely selfless request. I learn a lot by reading other people’s code. Mike’s use of SET instead of SELECT INTO is just one example.
IBM Documentation – SET Variable
IBM Documentation – SELECT FROM
IBM’s other Big Iron on the horizon
Reliability: and added value of IBM certified pre-owned vehicles