• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • 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

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags: Tags: 400guru, FHG, Four Hundred Guru, IBM i, RPG, SQL, SQL SET

    Sponsored by
    OCEAN User Group

    OCEAN TechCon25 Online

    It’s an Exciting Time for IBM i !

    July 16 & 17, 2025 – ONLINE

    Two virtual days of learning, presented by an outstanding group of IBM’ers and IBM Champions, featuring leading-edge topics.

    FREE for OCEAN members!

    Register NOW!

    Annual (12-month) Individual OCEAN Memberships are $80 and a Corporate Membership is $250. A Corporate Membership would allow your entire company to have full access to the OCEAN website & video library and to attend OCEAN events at member rates. Act now because rates are increasing on August 1, 2025.

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    The Other IBM Big Iron That Is On The Horizon Reliability: An Added Value Of IBM Certified Pre-Owned

    2 thoughts on “Guru: Set Beats A Loop”

    • Phil says:
      August 23, 2021 at 12:05 pm

      Hi Ted, I find your articles informative and helpful. For years I have used SQL almost exclusively. I have been fighting an uphill battle with some of my peers that refuse to see the benefits. They are always trying to come up with reasons why traditional processing is better than SQL. What are some proven tactics to use when debating these stubborn types?

      Reply
    • Ted Holt says:
      August 24, 2021 at 11:29 am

      I wish I knew the answer to Phil’s question.

      Reply

    Leave a Reply Cancel reply

TFH Volume: 31 Issue: 54

This Issue Sponsored By

  • Fresche Solutions
  • IBM
  • Profound Logic
  • Comarch
  • Raz-Lee Security

Table of Contents

  • Public Cloud Dreams Becoming A Reality for IBM i Users
  • Reliability: An Added Value Of IBM Certified Pre-Owned
  • Guru: Set Beats A Loop
  • The Other IBM Big Iron That Is On The Horizon
  • IBM i PTF Guide, Volume 23, Number 34

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • With Power11, Power Systems “Go To Eleven”
  • With Subscription Price, IBM i P20 And P30 Tiers Get Bigger Bundles
  • Izzi Buys CNX, Eyes Valence Port To System Z
  • IBM i Shops “Attacking” Security Concerns, Study Shows
  • IBM i PTF Guide, Volume 27, Number 26
  • Liam Allan Shares What’s Coming Next With Code For IBM i
  • From Stable To Scalable: Visual LANSA 16 Powers IBM i Growth – Launching July 8
  • VS Code Will Be The Heart Of The Modern IBM i Platform
  • The AS/400: A 37-Year-Old Dog That Loves To Learn New Tricks
  • IBM i PTF Guide, Volume 27, Number 25

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2025 IT Jungle