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|
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.