Guru: Set Beats A Loop
August 23, 2021 Ted Holt
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?
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 know him. I was delighted to see that he is moving away from record-level access and toward SQL. I get a good bit of email from readers who want to use SQL in their programs, but have run into a problem of some sort. Maybe it’s finally time I addressed that topic.
First, we need a little 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 look at the kind of code Mike said he doesn’t want to write any more.
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 this is the sort of source code Mike sent along after we had worked on it a bit.
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 quite. If there is at least one active (i.e., not deleted) row for the desired order, yes, the two yield 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’re equivalent. Wasn’t that easy?
Something else intrigued me. Mike had used the SET statement. Out of a habit hardened by years of embedding SQL in 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';
That set me to wondering if there is any difference between SET and SELECT INTO. I found one 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 embedded in an application program. It is an executable statement that can be dynamically prepared if all variables being set are global variables. It must not be specified in REXX.
[SELECT INTO] can only be embedded in an application program. It is an executable statement that cannot be dynamically prepared. It must not be specified in REXX.
Not much, is it? I wrote a few short test programs and both SET and SELECT INTO behaved identically.
The answer to Mike’s question is a definite YES! This is definitely a good practice.
It’s hard for me to believe that so many shops still don’t use SQL in their programs, but during the past few years, I have seen enough to convince me that the code currently being written in many shops looks like what I used to write in the 1990s. Hearing from 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 it is.
If you’re struggling with SQL, send me your code and I’ll try to help. Most people who email me with SQL problems are close to the solution and it’s not hard to put them on the right path. I won’t make promises, but I’ll help if I can. This is not a completely unselfish request. I learn a lot from reading other people’s code. Mike’s use of SET instead of SELECT INTO is only one example.