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.