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 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.

RELATED RESOURCES

IBM Documentation – SET Variable

IBM Documentation – SELECT INTO