• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Guru: SQL Checks For Control Breaks

    April 6, 2020 Ted Holt

    A control break occurs when the combined value of one or more fields changes from one row (record) to the next when reading a data set sequentially. I used to write RPG programs with control breaks often. Now that reports are less common, I write them less often, but that’s not to say I never write a program with control breaks.

    When I first learned to handle control breaks in RPG, I used the L1 through L9 level indicators. These worked wonderfully and fed my family for several years. When I moved from the System/36 world to the S/38 (and later, the AS/400), I followed the herd and wrote my RPG III programs without the cycle. Bye-bye, level indicators. It was good while it lasted.

    Now we come to the twenty-first century. I use SQL for I/O every chance I get. Has my processing of control breaks changed? As a matter of fact, it has. Just as the RPG cycle could alert me to control breaks, so can SQL. Just as I liked having the RPG cycle take care of this chore on my behalf, so I like the fact that SQL does the same.

    Let’s start with a table of sales orders sequenced by date of sale and item number. We’d like to see the orders in a grid within a browser, and to make the screen a little easier to read, we want to leave a blank line after each date, like this:

    If you’ve been writing control break logic without the cycle, you’re tempted to define a date variable that you can save the value of sale date for comparison to the next row. You don’t have to do that because SQL can do that for you. The two functions you need are LAG and LEAD.

    LAG tells you when the current row is the first of a control group.

    LEAD tells you when the current row is the last of a control group.

    Here’s some code that illustrates these functions:

    select lag(DateSold)
                  over (partition by DateSold
                            order by DateSold, Item)
              as PreviousDate,            
           DateSold,
           lead(DateSold)
                  over( partition by DateSold
                            order by DateSold, Item)
              as NextDate,            
           Item, Customer, Quantity
      from sales
     order by DateSold, Item
    

    And here’s the result set:

    Previous date Date of sale Next date Item Customer Quantity
    – 2020-04-06 2020-04-06 A1 Billy Rubin 12
    2020-04-06 2020-04-06 2020-04-06 A1 Pete Moss 3
    2020-04-06 2020-04-06 2020-04-06 A1 Polly Fonnick 5
    2020-04-06 2020-04-06 2020-04-06 D2 Pete Moss 18
    2020-04-06 2020-04-06 – D2 Jim Nazium 1
    – 2020-04-07 2020-04-07 D2 Nick O’Thyme 1
    2020-04-07 2020-04-07 2020-04-07 H7 Pete Moss 2
    2020-04-07 2020-04-07 – H7 Sue Doe-Nymm 7
    – 2020-04-08 – A1 Sam O’Varr 4
    – 2020-04-09 2020-04-09 A1 Tom Bolo 2
    2020-04-09 2020-04-09 2020-04-09 A1 Jack Aranda 8
    2020-04-09 2020-04-09 – D2 Polly Fonnick 7

    What has SQL done for us?

    The LAG function looks at the previous row in the result set. Each row that has a null previous date is the first row for that date.

    The LEAD function looks at the following row in the result set. Each row that has a null next date is the last row for that date.

    Is that powerful or what?

    Here are the significant portions of the code that loaded that grid.

    dcl-f Display workstn usropn
                          extdesc('YG001D')
                          extfile(*EXTDESC)
                          qualified
                          alias
                          sfile(SalesGrid : rrn);
    
    dcl-ds   Sales_grid_ds_t  
                likerec(Display.SalesGrid : *all)
                template;
    
    dcl-ds HostData            qualified;
       DateSold         date;
       NextDate         date;
       Item             char   ( 2);
       Customer         char   (16);
       Quantity         packed ( 3);
    end-ds HostData;
    
    dcl-s  Indicators   int(5)  dim(5);             
    
    dcl-ds Sales_grid_ds       LikeDS(Sales_grid_ds_t);   
    
    exec sql declare c1 cursor for
    
       select DateSold,
              lead(DateSold)
                     over( partition by DateSold
                               order by DateSold, Item)
                 as NextDate,
              Item, Customer, Quantity
         from ygsales
        order by DateSold, Item;
    
        exec sql  open c1;
    
        dow '1';
           exec sql  fetch c1 into :HostData :Indicators;
           if (SqlState >= '02000');
              leave;
           endif;
           eval-corr  Sales_grid_ds = HostData;
           rrn += 1;
           write Display.SalesGrid  Sales_grid_ds;
    
           if Indicators (2) < *zero;
              clear Sales_grid_ds;
              rrn += 1;
              write Display.SalesGrid  Sales_grid_ds;
           endif;
        enddo;
    
        exec sql  close c1;
    

    When the second indicator variable is negative, the second field of HostData — NextDate — is null. I know to write a blank row to the subfile.

    I did not give up the RPG cycle out of dissatisfaction. I appreciated the way it did so much for me. But times changed and I changed with them. Well, friend, times have changed again.

    RELATED STORIES

    Guru: Emulate Control Break Reporting With DB2 For i SQL

    LAG And LEAD Functions In DB2 for i 7.3

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags: Tags: 400guru, AS/400, FHG, Four Hundred Guru, IBM i, RPG, RPG III, S/38, SQL, System/36

    Sponsored by
    Maxava

    Migrate IBM i with Confidence

    Tired of costly and risky migrations? Maxava Migrate Live minimizes disruption with seamless transitions. Upgrading to Power10 or cloud hosted system, Maxava has you covered!

    Learn More

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Unperspective: To Which Side Do You Belong? The IT Sector Could Weather The Pandemic Storm

    2 thoughts on “Guru: SQL Checks For Control Breaks”

    • Mattias L says:
      April 6, 2020 at 7:10 am

      This will come in handy. One of those things I didn’t know I needed until I saw it…

      Reply
    • michael a smith says:
      April 17, 2020 at 10:26 am

      Ted,could you show how to use this for multiple breaks. ie add item as a second break

      Reply

    Leave a Reply Cancel reply

TFH Volume: 30 Issue: 22

This Issue Sponsored By

  • Maxava
  • WorksRight Software
  • ARCAD Software
  • Computer Keyes
  • Manta Technologies

Table of Contents

  • Big Blue Changes Gears Slightly With IBM i TR Plan
  • The IT Sector Could Weather The Pandemic Storm
  • Guru: SQL Checks For Control Breaks
  • Unperspective: To Which Side Do You Belong?
  • Final IBM i Software Maintenance Price Increases Released

Content archive

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

Recent Posts

  • POWERUp 2025 –Your Source For IBM i 7.6 Information
  • Maxava Consulting Services Does More Than HA/DR Project Management – A Lot More
  • Guru: Creating An SQL Stored Procedure That Returns A Result Set
  • As I See It: At Any Cost
  • IBM i PTF Guide, Volume 27, Number 19
  • IBM Unveils Manzan, A New Open Source Event Monitor For IBM i
  • Say Goodbye To Downtime: Update Your Database Without Taking Your Business Offline
  • i-Rays Brings Observability To IBM i Performance Problems
  • Another Non-TR “Technology Refresh” Happens With IBM i TR6
  • IBM i PTF Guide, Volume 27, Number 18

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