• 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
    WorksRight Software

    Do you need area code information?
    Do you need ZIP Code information?
    Do you need ZIP+4 information?
    Do you need city name information?
    Do you need county information?
    Do you need a nearest dealer locator system?

    We can HELP! We have affordable AS/400 software and data to do all of the above. Whether you need a simple city name retrieval system or a sophisticated CASS postal coding system, we have it for you!

    The ZIP/CITY system is based on 5-digit ZIP Codes. You can retrieve city names, state names, county names, area codes, time zones, latitude, longitude, and more just by knowing the ZIP Code. We supply information on all the latest area code changes. A nearest dealer locator function is also included. ZIP/CITY includes software, data, monthly updates, and unlimited support. The cost is $495 per year.

    PER/ZIP4 is a sophisticated CASS certified postal coding system for assigning ZIP Codes, ZIP+4, carrier route, and delivery point codes. PER/ZIP4 also provides county names and FIPS codes. PER/ZIP4 can be used interactively, in batch, and with callable programs. PER/ZIP4 includes software, data, monthly updates, and unlimited support. The cost is $3,900 for the first year, and $1,950 for renewal.

    Just call us and we’ll arrange for 30 days FREE use of either ZIP/CITY or PER/ZIP4.

    WorksRight Software, Inc.
    Phone: 601-856-8337
    Fax: 601-856-9432
    Email: software@worksright.com
    Website: www.worksright.com

    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

  • How Committed Is Big Blue To The IBM Cloud?
  • Immutable Copies Are Only As Good As Your Validation
  • Guru: IBM i *USRPRF Security
  • ERP Transitions Loom for SAP on IBM i Customers
  • Inflation Pumps Up Global IT Spending, Supply Chain Deflates It
  • COMMON Set for First Annual Conference in Three Years
  • API Operations Management for Safe, Powerful, and High Performance APIs
  • What’s New in IBM i Services and Networking
  • Four Hundred Monitor, May 18
  • IBM i PTF Guide, Volume 24, Number 20

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 © 2022 IT Jungle

loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.