• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Guru: SQL PL, WHILE And REPEAT Loops

    January 18, 2017 Ted Holt

    In earlier editions of this august publication, I covered the SQL PL looping structures that I consider to be the most useful. Today I cover the remaining looping structures. I cover them for completeness, but also because what I consider to be most useful may not be what you consider to be most useful.

    To review, the FOR loop provides a simple way to process the rows of a result set. LOOP structure is versatile, providing the freedom to exit a loop from any point. The remaining loop structures are WHILE and REPEAT.

    The WHILE loop is a top-tested loop. It runs as long as a condition is true. If the governing condition is false when control first reaches the loop, the loop will not execute at all.

    The WHILE syntax is simple:

    WHILE condition DO body END WHILE

    The REPEAT loop is a bottom-tested loop. It runs until a condition is true. A REPEAT loop executes at least one time. The syntax of REPEAT is also simple.

    REPEAT body UNTIL condition END REPEAT

    RPG programmers will recognize these structures as the counterparts to the DOW and DOU op codes.

    To illustrate, let me return to a topic I wrote about awhile back, namely the need to convert a list into a temporary table. Here’s a routine that creates such a procedure.

    create or replace procedure BuildDepartmentList
       (in p_List   varchar(256))
    
    begin
       declare v_Department      dec     (  3);
       declare v_List            varchar (257);
       declare v_Pos             integer;
       declare v_Sep             varchar (  1)   default ',';
    
       set  v_List = trim(p_List) concat v_Sep;
    
       declare global temporary table DeptList
         ( Department     dec(2) )
       with replace;
    
       while v_List <> ' ' do
          set v_Pos = Locate (v_Sep, v_List);
          set v_Department = dec (substr ( v_List, 1, v_Pos - 1 ) );
          insert into session.DeptList values(v_Department);
          set v_List = substr (v_List, v_Pos + 1);
       end while;
    end
    

    And here’s how I call the procedure:

    call BuildDepartmentList ('3,4,9,11,69')
    

    When BuildDepartmentList starts running, p_List has the value <i>3,4,9,11,69</i>. The first SET copies the value of parameter p_List into v_List and adds a comma to the end.

    set  v_List = trim(p_List) concat v_Sep;
    

    This provides a work variable that the program can modify and ensures that the first SET within the WHILE loop always loads a positive value into v_Pos.

    The WHILE loop runs as long as v_List is not blank. After the first iteration:

    • v_Pos is 2
    • v_Department is 3
    • the DeptList table has a row with the value 3</li>
    • v_List has become 4,9,11,69

    Each iteration removes the first number from v_List and writes that number into the DeptList table. The loop ends after the fifth iteration, when v_List has a blank value.

    The body of the WHILE executes five times and DeptList has five rows (records). Now I can use the temporary table for row selection.

    select * 
      from GLXACTS
     where Dept in (select Department from session.DeptList)
    

    A WHILE loop is a WHILE loop is a WHILE loop, regardless of the syntax of a programming language. The SQL PL syntax for top-tested and bottom-tested loops is as simple as any.

     

    Ted Holt welcomes your comments and questions. Email him through the IT Jungle Contacts page.

     

    RELATED STORIES

    SQL PL–The LOOP Loop

    SQL PL–The FOR Loop

    Dynamic Lists In Static SQL Queries

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    DRV Tech

    Get More Out of Your IBM i

    With soaring costs, operational data is more critical than ever. IBM shops need faster, easier ways to distribute IBM applications-based data to users more efficiently, no matter where they are.

    The Problem:

    For Users, IBM Data Can Be Difficult to Get To

    IBM Applications generate reports as spooled files, originally designed to be printed. Often those reports are packed together with so much data it makes them difficult to read. Add to that hardcopy is a pain to distribute. User-friendly formats like Excel and PDF are better, offering sorting, searching, and easy portability but getting IBM reports into these formats can be tricky without the right tools.

    The Solution:

    IBM i Reports can easily be converted to easy to read and share formats like Excel and PDF and Delivered by Email

    Converting IBM i, iSeries, and AS400 reports into Excel and PDF is now a lot easier with SpoolFlex software by DRV Tech.  If you or your users are still doing this manually, think how much time is wasted dragging and reformatting to make a report readable. How much time would be saved if they were automatically formatted correctly and delivered to one or multiple recipients.

    SpoolFlex converts spooled files to Excel and PDF, automatically emailing them, and saving copies to network shared folders. SpoolFlex converts complex reports to Excel, removing unwanted headers, splitting large reports out for individual recipients, and delivering to users whether they are at the office or working from home.

    Watch our 2-minute video and see DRV’s powerful SpoolFlex software can solve your file conversion challenges.

    Watch Video

    DRV Tech

    www.drvtech.com

    866.378.3366

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Mad Dog 21/21: As Ginni Bows Out What Will This Year Bring For IBM i?

    2 thoughts on “Guru: SQL PL, WHILE And REPEAT Loops”

    • Dan Lovell says:
      January 19, 2017 at 9:29 am

      Very good article! Another loop construct is the FOR x AS cursor_name CURSOR FOR SELECT COL1, COL2 FROM TABLEx WHERE COL1 = ‘ABC’ DO END FOR; In this construct for each row read processing is done.

      Reply
    • Ted Holt says:
      February 23, 2017 at 1:56 pm

      Thanks, Dan.

      See https://www.itjungle.com/2016/11/15/fhg111516-story02/ for an article about FOR loops.

      Reply

    Leave a Reply Cancel reply

TFH Volume: 27 Issue: 1

This Issue Sponsored By

  • Profound Logic Software
  • Chrono-Logic
  • WorksRight Software
  • System i Developer
  • Manta Technologies

Table of Contents

  • A Power Systems Wish List For The Year Ahead
  • What Will This Year Bring For IBM i?
  • Guru: SQL PL, WHILE And REPEAT Loops
  • Mad Dog 21/21: As Ginni Bows Out
  • IBM i Open Source Business Architect Lays Out A Plan

Content archive

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

Recent Posts

  • The Power11 Transistor Count Discrepancies Explained – Sort Of
  • Is Your IBM i HA/DR Actually Tested – Or Just Installed?
  • Big Blue Delivers IBM i Customer Requests In ACS Update
  • New DbToo SDK Hooks RPG And Db2 For i To External Services
  • IBM i PTF Guide, Volume 27, Number 33
  • Tool Aims To Streamline Git Integration For Old School IBM i Devs
  • IBM To Add Full System Replication And FlashCopy To PowerHA
  • Guru: Decoding Base64 ASCII
  • The Price Tweaking Continues For Power Systems
  • IBM i PTF Guide, Volume 27, Numbers 31 And 32

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