Arranging Multiple Break Columns
As you can see in the previous example, you can also have multiple customers within a city. You can apply the same break concept to the city column to make this grouping of customers more apparent. Add another ON-BREAK to the program so that city is also printed only when its value changes.
When you have multiple breaks, you must arrange them in a hierarchy. In the sample program, the breaks are for geographical units, so arranging them according to size is logical: first state and then city. This sort of arrangement is called nesting, and the breaks are considered nested.
To ensure that the breaks are properly nested, use the LEVEL keyword. This argument numbers breaks by level and specifies that the columns are printed in order of increasing break levels, from left to right. Number breaks in the same order in which they are sorted in the ORDER BY clause.
See Setting Break Procedures with BEFORE and AFTER Qualifiers.
The LEVEL argument enables you to control the order in which you call break procedures. The next sample program is identical to ex5a.sqr except for the two lines that print the state and city columns, which are shown in this way:
Program ex5c.sqr
begin-program
do list_customers
end-program
begin-heading 2
print 'State' (1,1)
print 'City' (1,7)
print 'Name' (1,24)
print 'Phone' (1,55)
end-heading
begin-procedure list_customers
begin-select
state (,1) on-break level=1
city (,7) on-break level=2
name (,24)
phone (,55)
position (+1) ! Advance to the next line
from customers
order by state, city, name
end-select
end-procedure ! list_customers
The output for the ex5c.sqr program is:
State City Name Phone
IN Davenport Harold Alexander Fink 3015553645
Miningville Harry's Landmark Diner 3175550948
MI Bell Harbor Sam Johnson 3135556732
NH Frogline Jerry's Junkyard Specialties 6125552877
NJ Teaneck Clair Butterfield 2015559901
NM Big Falls Joe Smith and Company 8085552124
NY Mamaroneck Harriet Bailey 9145550144
New York John Conway 2125552311
Corks and Bottles, Inc. 2125550021
Kate's Out of Date Dress Shop 2125559000
Queens Eliot Richards 2125554285
OH Cleveland Quentin Fields 2165553341
Everretsville Gregory Stonehaven 2165553109
Zanesville Isaiah J Schwartz and Company 5185559813
As you can see, three customers are in New York, so the city name for the second and third customers is left blank.