Setting Break Procedures with BEFORE and AFTER Qualifiers
When you print variables with ON-BREAK, you can automatically call procedures before and after each break in a column. The BEFORE and AFTER qualifiers provide this capability. For example:
begin-select
state (,1) on-break before=state_heading after=state_totThe BEFORE qualifier automatically calls the state_heading procedure to print headings before each group of records of the same state. Similarly, the AFTER qualifier automatically calls the state_tot procedure to print totals after each group of records.
All BEFORE procedures are automatically invoked before each break, including the first; that is, before the select paragraph is even processed. Similarly, all AFTER procedures are invoked after each break, including the last group; that is, upon completion of the select paragraph.
Order of Events
You can define a hierarchy of break columns by using the LEVEL qualifier of ON-BREAK. In the ex5c.sqr sample program, for example, state was defined as LEVEL=1 and city as LEVEL=2.
When a break occurs at one level, it also forces breaks on variables with higher LEVEL qualifiers. In the sample program, a break on state also means a break on city.
A break on a variable can initiate many other events. The value can be printed, lines can be skipped, procedures can be called automatically, and the old value can be saved. Knowing the order of events is important, particularly when multiple ON-BREAK columns exist.
The following select paragraph has breaks on three levels:
begin-select
state (,1) on-break level=1 after=state_tot skiplines=2
city (,7) on-break level=2 after=city_tot skiplines=1
zip (,45) on-break level=3 after=zip_tot
from customers
order by state, city, zip
end-selectThe system processes breaks in the following way:
When zip breaks, the city_tot procedure is run.
When city breaks, first the zip_tot procedure is run, and then the city_tot procedure is run and one line is skipped (SKIPLINES=1).
Both city and zip are printed in the next record.
When state breaks, the zip_tot, city_tot, and state_tot procedures are processed in that order.
One line is skipped after the city_tot procedure is run, and two lines are skipped after the state_tot procedure is run. All three columns—state, city, and zip—are printed in the next record.
The following program (ex5e.sqr) demonstrates the order of events in break processing. It has three ON-BREAK columns, each with a LEVEL argument and a BEFORE and AFTER procedure. The BEFORE and AFTER procedures print strings to indicate the order of processing.
Program ex5e.sqr
begin-setup
declare-Layout
default
end-declare
end-setup
begin-program
do main
end-program
begin-procedure a
print 'AFTER Procedure for state LEVEL 1' (+1,40)
end-procedure
begin-procedure b
print 'AFTER Procedure city LEVEL 2' (+1,40)
end-procedure
begin-procedure c
print 'AFTER Procedure zip LEVEL 3' (+1,40)
end-procedure
begin-procedure aa
print 'BEFORE Procedure state LEVEL 1' (+1,40)
end-procedure
begin-procedure bb
print 'BEFORE Procedure city LEVEL 2' (+1,40)
end-procedure
begin-procedure cc
print 'BEFORE Procedure zip LEVEL 3' (+1,40)
end-procedure
begin-procedure main local
begin-select
add 1 to #count
print 'Retrieved row #' (+1,40)
print #count (,+10)Edit 9999
position (+1)
state (3,1) On-Break Level=1 after=a before=aa
city (3,10) On-Break Level=2 after=b before=bb
zip (3,25) On-Break Level=3 after=c before=cc Edit xxxxx
next-listing Need=10
from customers
order by state,city,zip
end-select
end-procedure
begin-heading 3
print $current-date (1,1) edit 'DD-MM-YYYY'
page-number (1,60) 'Page '
last-page () ' of '
print 'STATE' (3,1)
print 'CITY' (3,10)
print 'ZIP' (3,25)
print 'Break Processing sequence' (3,40)
end-heading
The output for the ex5e.sqr program is:
02-05-2004 Page 1 of 3
STATE CITY ZIP Break Processing sequence
BEFORE Procedure state LEVEL 1
IN Davenport 62130 BEFORE Procedure city LEVEL 2
BEFORE Procedure zip LEVEL 3
Retrieved row #1
Retrieved row #2
Miningville 40622
AFTER Procedure zip LEVEL 3
AFTER Procedure city LEVEL 2
BEFORE Procedure city LEVEL 2
BEFORE Procedure zip LEVEL 3
Retrieved row #3
MI Bell Harbor 40674
AFTER Procedure zip LEVEL 3
AFTER Procedure city LEVEL 2
AFTER Procedure for state LEVEL 1
BEFORE Procedure state LEVEL 1
BEFORE Procedure city LEVEL 2
BEFORE Procedure zip LEVEL 3
Retrieved row #4
NH Frogline 04821
AFTER Procedure zip LEVEL 3
AFTER Procedure city LEVEL 2
AFTER Procedure for state LEVEL 1
BEFORE Procedure state LEVEL 1
BEFORE Procedure city LEVEL 2
BEFORE Procedure zip LEVEL 3
Retrieved row #5
NJ Teaneck 00355
AFTER Procedure zip LEVEL 3
AFTER Procedure city LEVEL 2
AFTER Procedure for state LEVEL 1
BEFORE Procedure state LEVEL 1
BEFORE Procedure city LEVEL 2
BEFORE Procedure zip LEVEL 3
Retrieved row #6
NM Big Falls 87893
AFTER Procedure zip LEVEL 3
AFTER Procedure city LEVEL 2
AFTER Procedure for state LEVEL 1
BEFORE Procedure state LEVEL 1
BEFORE Procedure city LEVEL 2
BEFORE Procedure zip LEVEL 3
02-05-2004 Page 2 of 3
STATE CITY ZIP Break Processing sequence
Retrieved row #7
NY Mamaroneck 10833
AFTER Procedure zip LEVEL 3
AFTER Procedure city LEVEL 2
AFTER Procedure for state LEVEL 1
BEFORE Procedure state LEVEL 1
BEFORE Procedure city LEVEL 2
BEFORE Procedure zip LEVEL 3
...
The following steps explain the order of processing in detail:
Process BEFORE procedures.
BEFORE procedures are processed in ascending order by LEVEL before the first row of the query is retrieved. If no data is selected, BEFORE procedures are not run.
Select the first row of data.
Select subsequent rows of data.
Processing of the select paragraph continues. When a break occurs on any column, it also initiates breaks on columns at the same or higher levels. Events occur in the following order:
AFTER procedures are processed in descending order from the highest level to the level of the current ON-BREAK column.
SAVE variables are set with the value of the previous ON-BREAK column.
BEFORE procedures are processed in ascending order from the current level to the highest level.
If SKIPLINES was specified, the current line position is advanced.
The value of the new group is printed (unless PRINT=NEVER is specified).
Process AFTER procedures.
After the select paragraph is complete, if any rows were selected, AFTER procedures are processed in descending order by LEVEL.