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:

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