This document describes how the execution of a statement can be made dependent on a control break, and how control breaks can be used for the evaluation of Natural system functions.
The following topics are covered:
A control break occurs when the value of a control field changes.
The execution of statements can be made dependent on a control break.
A control break can also be used for the evaluation of Natural system functions.
System functions are discussed in System Variables and System Functions. For detailed descriptions of the system functions available, refer to the System Functions documentation.
With the statement AT BREAK
,
you specify the processing which is to be performed whenever a control break occurs, that
is, whenever the value of a control field which you specify with the AT BREAK
statement changes. As a control field, you can use a database field or a user-defined
variable.
The following topics are covered below:
The field specified as control field in an AT
BREAK
statement is usually a database field.
Example:
... AT BREAK OF DEPT statements END-BREAK ...
In this example, the control field is the database field DEPT
; if the
value of the field changes, for example, FROM
SALE01
to SALE02
, the statements
specified in the AT BREAK
statement would be executed.
Instead of an entire field, you can also use only part of a field as a control field.
With the slash-n-slash notation
/n/
, you can determine that only the first
n positions of a field are to be checked for a change in
value.
Example:
... AT BREAK OF DEPT /4/ statements END-BREAK ...
In this example, the specified statements would only be
executed if the value of the first 4 positions of the field DEPT
changes,
for example, FROM SALE
to TECH
; if, however, the field value
changes from SALE01
to SALE02
, this would be ignored and no
AT BREAK
processing performed.
** Example 'ATBREX01': AT BREAK OF (with database field) ************************************************************************ DEFINE DATA LOCAL 1 MYVIEW VIEW OF EMPLOYEES 2 NAME 2 CITY 2 COUNTRY 2 JOB-TITLE 2 SALARY (1:1) END-DEFINE * READ (5) MYVIEW BY CITY WHERE COUNTRY = 'USA' DISPLAY CITY (AL=9) NAME 'POSITION' JOB-TITLE 'SALARY' SALARY(1) /* AT BREAK OF CITY WRITE / OLD(CITY) (EM=X^X^X^X^X^X^X^X^X^X^X^) 5X 'AVERAGE:' T*SALARY AVER(SALARY(1)) // COUNT(SALARY(1)) 'RECORDS FOUND' / END-BREAK /* AT END OF DATA WRITE 'TOTAL (ALL RECORDS):' T*SALARY(1) TOTAL(SALARY(1)) END-ENDDATA END-READ END
In the above program, the first WRITE
statement is executed whenever the value of the field
CITY
changes.
In the AT BREAK
statement,
the Natural system functions OLD
, AVER
and COUNT
are evaluated (and output in the WRITE
statement).
In the AT END OF DATA
statement, the Natural system function TOTAL
is evaluated.
Output of Program ATBREX01
:
Page 1 14-01-14 14:07:26 CITY NAME POSITION SALARY --------- -------------------- ------------------------- ---------- AIKEN SENKO PROGRAMMER 31500 A I K E N AVERAGE: 31500 1 RECORDS FOUND ALBUQUERQ HAMMOND SECRETARY 22000 ALBUQUERQ ROLLING MANAGER 34000 ALBUQUERQ FREEMAN MANAGER 34000 ALBUQUERQ LINCOLN ANALYST 41000 A L B U Q U E R Q U E AVERAGE: 32750 4 RECORDS FOUND TOTAL (ALL RECORDS): 162500
A user-defined variable can also be used as
control field in an AT BREAK
statement.
In the following program, the user-defined variable
#LOCATION
is used as control field.
** Example 'ATBREX02': AT BREAK OF (with user-defined variable and ** in conjunction with BEFORE BREAK PROCESSING) ************************************************************************ DEFINE DATA LOCAL 1 MYVIEW VIEW OF EMPLOYEES 2 CITY 2 COUNTRY 2 JOB-TITLE 2 SALARY (1:1) * 1 #LOCATION (A20) END-DEFINE * READ (5) MYVIEW BY CITY WHERE COUNTRY = 'USA' BEFORE BREAK PROCESSING COMPRESS CITY 'USA' INTO #LOCATION END-BEFORE DISPLAY #LOCATION 'POSITION' JOB-TITLE 'SALARY' SALARY (1) /* AT BREAK OF #LOCATION SKIP 1 END-BREAK END-READ END
Output of Program ATBREX02
:
Page 1 14-01-14 14:08:36 #LOCATION POSITION SALARY -------------------- ------------------------- ---------- AIKEN USA PROGRAMMER 31500 ALBUQUERQUE USA SECRETARY 22000 ALBUQUERQUE USA MANAGER 34000 ALBUQUERQUE USA MANAGER 34000 ALBUQUERQUE USA ANALYST 41000
As explained above, the notation /n/
allows
some portion of a field to be checked for a control break. It is possible to combine
several AT BREAK
statements,
using an entire field as control field for one break and part of the same field as
control field for another break.
In such a case, the break at the lower level (entire field) must be specified before
the break at the higher level (part of field); that is, in the first AT
BREAK
statement the entire field must be specified as control field, and in the
second one part of the field.
The following example program illustrates this, using the field DEPT
as
well as the first 4 positions of that field (DEPT /4/
).
** Example 'ATBREX03': AT BREAK OF (two statements in combination) ************************************************************************ DEFINE DATA LOCAL 1 MYVIEW VIEW OF EMPLOYEES 2 NAME 2 JOB-TITLE 2 DEPT 2 SALARY (1:1) 2 CURR-CODE (1:1) END-DEFINE * READ MYVIEW BY DEPT STARTING FROM 'SALE40' ENDING AT 'TECH10' WHERE SALARY(1) GT 47000 AND CURR-CODE(1) = 'USD' /* AT BREAK OF DEPT WRITE '*** LOWEST BREAK LEVEL ***' / END-BREAK AT BREAK OF DEPT /4/ WRITE '*** HIGHEST BREAK LEVEL ***' END-BREAK /* DISPLAY DEPT NAME 'POSITION' JOB-TITLE END-READ END
Output of Program ATBREX03
:
Page 1 14-01-14 14:09:20 DEPARTMENT NAME POSITION CODE ---------- -------------------- ------------------------- TECH05 HERZOG MANAGER TECH05 LAWLER MANAGER TECH05 MEYER MANAGER *** LOWEST BREAK LEVEL *** TECH10 DEKKER DBA *** LOWEST BREAK LEVEL *** *** HIGHEST BREAK LEVEL ***
In the following program, one blank line is output whenever the value of the field
DEPT
changes; and whenever the value in the first 4 positions of
DEPT
changes, a record count is carried out by evaluating the system
function COUNT
.
** Example 'ATBREX04': AT BREAK OF (two statements in combination) ************************************************************************ DEFINE DATA LOCAL 1 MYVIEW VIEW OF EMPLOYEES 2 DEPT 2 REDEFINE DEPT 3 #GENDEP (A4) 2 NAME 2 SALARY (1) END-DEFINE * WRITE TITLE '** PERSONS WITH SALARY > 30000, SORTED BY DEPARTMENT **' / LIMIT 9 READ MYVIEW BY DEPT FROM 'A' WHERE SALARY(1) > 30000 DISPLAY 'DEPT' DEPT NAME 'SALARY' SALARY(1) /* AT BREAK OF DEPT SKIP 1 END-BREAK AT BREAK OF DEPT /4/ WRITE COUNT(SALARY(1)) 'RECORDS FOUND IN:' OLD(#GENDEP) / END-BREAK END-READ END
Output of Program ATBREX04
:
** PERSONS WITH SALARY > 30000, SORTED BY DEPARTMENT ** DEPT NAME SALARY ------ -------------------- ---------- ADMA01 JENSEN 180000 ADMA01 PETERSEN 105000 ADMA01 MORTENSEN 320000 ADMA01 MADSEN 149000 ADMA01 BUHL 642000 ADMA02 HERMANSEN 391500 ADMA02 PLOUG 162900 ADMA02 HANSEN 234000 8 RECORDS FOUND IN: ADMA COMP01 HEURTEBISE 168800 1 RECORDS FOUND IN: COMP
Automatic break processing is in effect for a processing loop which contains an AT BREAK
statement. This applies to the
following statements:
The value of the control field specified with the AT BREAK
statement is checked only for records which satisfy
the selection criteria of both the WITH
clause and the WHERE
clause.
Natural system functions (AVER
, MAX
, MIN
, etc.) are
evaluated for each record after all statements within the processing loop have been
executed. System functions are not evaluated for any record which is rejected by WHERE
criteria.
The figure below illustrates the flow logic of automatic break processing.
The following example shows the use of the Natural
system functions
OLD
, MIN
, AVER
, MAX
, SUM
and COUNT
in an AT BREAK
statement (and of the system
function TOTAL
in an
AT END OF DATA
statement).
** Example 'ATBREX05': AT BREAK OF (with system functions) ************************************************************************ DEFINE DATA LOCAL 1 MYVIEW VIEW OF EMPLOYEES 2 NAME 2 CITY 2 SALARY (1:1) 2 CURR-CODE (1:1) END-DEFINE * LIMIT 3 READ MYVIEW BY CITY = 'SALT LAKE CITY' DISPLAY NOTITLE CITY NAME 'SALARY' SALARY(1) 'CURRENCY' CURR-CODE(1) /* AT BREAK OF CITY WRITE / OLD(CITY) (EM=X^X^X^X^X^X^X^X^X^X^X^X^X^X^X) 31T ' - MINIMUM:' MIN(SALARY(1)) CURR-CODE(1) / 31T ' - AVERAGE:' AVER(SALARY(1)) CURR-CODE(1) / 31T ' - MAXIMUM:' MAX(SALARY(1)) CURR-CODE(1) / 31T ' - SUM:' SUM(SALARY(1)) CURR-CODE(1) / 33T COUNT(SALARY(1)) 'RECORDS FOUND' / END-BREAK /* AT END OF DATA WRITE 22T 'TOTAL (ALL RECORDS):' T*SALARY TOTAL(SALARY(1)) CURR-CODE(1) END-ENDDATA END-READ END
Output of Program ATBREX05
:
CITY NAME SALARY CURRENCY -------------------- -------------------- ---------- -------- SALT LAKE CITY ANDERSON 50000 USD SALT LAKE CITY SAMUELSON 24000 USD S A L T L A K E C I T Y - MINIMUM: 24000 USD - AVERAGE: 37000 USD - MAXIMUM: 50000 USD - SUM: 74000 USD 2 RECORDS FOUND SAN DIEGO GEE 60000 USD S A N D I E G O - MINIMUM: 60000 USD - AVERAGE: 60000 USD - MAXIMUM: 60000 USD - SUM: 60000 USD 1 RECORDS FOUND TOTAL (ALL RECORDS): 134000 USD
See the following example program:
With the BEFORE BREAK PROCESSING
statement, you can specify statements that are to be executed immediately before a control
break; that is, before the value of the control field is checked, before the statements
specified in the AT BREAK
block
are executed, and before any Natural system
functions are evaluated.
** Example 'BEFORX01': BEFORE BREAK PROCESSING ************************************************************************ DEFINE DATA LOCAL 1 MYVIEW VIEW OF EMPLOYEES 2 NAME 2 FIRST-NAME 2 SALARY (1:1) 2 BONUS (1:1,1:1) * 1 #INCOME (P11) END-DEFINE * LIMIT 5 READ MYVIEW BY NAME FROM 'B' BEFORE BREAK PROCESSING COMPUTE #INCOME = SALARY(1) + BONUS(1,1) END-BEFORE /* DISPLAY NOTITLE NAME FIRST-NAME (AL=10) 'ANNUAL/INCOME' #INCOME 'SALARY' SALARY(1) (LC==) / '+ BONUS' BONUS(1,1) (IC=+) AT BREAK OF #INCOME WRITE T*#INCOME '-'(24) END-BREAK END-READ END
Output of Program BEFORX01
:
NAME FIRST-NAME ANNUAL SALARY INCOME + BONUS -------------------- ---------- ------------ ----------- BACHMANN HANS 56800 = 52800 +4000 ------------------------ BAECKER JOHANNES 81000 = 74400 +6600 ------------------------ BAECKER KARL 52650 = 48600 +4050 ------------------------ BAGAZJA MARJAN 152700 = 129700 +23000 ------------------------ BAILLET PATRICK 198500 = 188000 +10500 ------------------------
With automatic break processing, the statements specified in an AT BREAK
block are executed whenever the
value of the specified control field changes - regardless of the position of the AT
BREAK
statement in the processing loop.
With a PERFORM BREAK
PROCESSING
statement, you can perform break processing at a specified
position in a processing loop: the PERFORM BREAK PROCESSING
statement is
executed when it is encountered in the processing flow of the program.
Immediately after the PERFORM BREAK PROCESSING
, you specify one or more
AT BREAK
statement blocks:
... PERFORM BREAK PROCESSING AT BREAK OF field1 statements END-BREAK AT BREAK OF field2 statements END-BREAK ...
When a PERFORM BREAK PROCESSING
is executed, Natural checks if a break has
occurred; that is, if the value of the specified control field has changed; and if it has,
the specified statements are executed.
With PERFORM BREAK PROCESSING
, system functions are evaluated
before Natural checks if a break has occurred.
The following figure illustrates the flow logic of user-initiated break processing:
** Example 'PERFBX01': PERFORM BREAK PROCESSING (with BREAK option ** in IF statement) ************************************************************************ DEFINE DATA LOCAL 1 MYVIEW VIEW OF EMPLOYEES 2 NAME 2 DEPT 2 SALARY (1:1) * 1 #CNTL (N2) END-DEFINE * LIMIT 7 READ MYVIEW BY DEPT AT BREAK OF DEPT /* <- automatic break processing SKIP 1 WRITE 'SUMMARY FOR ALL SALARIES ' 'SUM:' SUM(SALARY(1)) 'TOTAL:' TOTAL(SALARY(1)) ADD 1 TO #CNTL END-BREAK /* IF SALARY (1) GREATER THAN 100000 OR BREAK #CNTL PERFORM BREAK PROCESSING /* <- user-initiated break processing AT BREAK OF #CNTL WRITE 'SUMMARY FOR SALARY GREATER 100000' 'SUM:' SUM(SALARY(1)) 'TOTAL:' TOTAL(SALARY(1)) END-BREAK END-IF /* IF SALARY (1) GREATER THAN 150000 OR BREAK #CNTL PERFORM BREAK PROCESSING /* <- user-initiated break processing AT BREAK OF #CNTL WRITE 'SUMMARY FOR SALARY GREATER 150000' 'SUM:' SUM(SALARY(1)) 'TOTAL:' TOTAL(SALARY(1)) END-BREAK END-IF DISPLAY NAME DEPT SALARY(1) END-READ END
Output of Program PERFBX01
:
Page 1 14-01-14 14:13:35 NAME DEPARTMENT ANNUAL CODE SALARY -------------------- ---------- ---------- JENSEN ADMA01 180000 PETERSEN ADMA01 105000 MORTENSEN ADMA01 320000 MADSEN ADMA01 149000 BUHL ADMA01 642000 SUMMARY FOR ALL SALARIES SUM: 1396000 TOTAL: 1396000 SUMMARY FOR SALARY GREATER 100000 SUM: 1396000 TOTAL: 1396000 SUMMARY FOR SALARY GREATER 150000 SUM: 1142000 TOTAL: 1142000 HERMANSEN ADMA02 391500 PLOUG ADMA02 162900 SUMMARY FOR ALL SALARIES SUM: 554400 TOTAL: 1950400 SUMMARY FOR SALARY GREATER 100000 SUM: 554400 TOTAL: 1950400 SUMMARY FOR SALARY GREATER 150000 SUM: 554400 TOTAL: 1696400