When a column in a SORT statement is enclosed in parentheses, it becomes a control break. Whenever the value contained in a control break column changes, a control break occurs.
Purpose
The primary use for control breaks is to specify when subtotals will be displayed for columns that have been designated for accumulation in the PRINT statement and to trigger processing of special functions defined in the WHEN/DO clause. (See Specifying Functions at Control Breaks for more information.) When the PRINT statement is executed, a subtotal for each accumulation column is presented whenever the value changes in the control break. After all data has been presented, a grand total appears as the last line of output.
You can also use control breaks to make reports easier reading. If the SUPPRESS DUPLICATE COLUMNS options is set to YES in your User Profile, the value contained in the control break is displayed only once on the screen. The value appears on the first line in the column designated for the control break. The column remains blank until the value in the control break changes.
You can designate up to ten control breaks in the SORT statement. Control breaks and ordinary columns can be freely intermixed.
Sample
The following query illustrates the use of control breaks. The control break column is ORD-ID, which is enclosed in parentheses in the SORT statement. Accumulation columns are not specified in this example.
FIND 10 CAI-SLSHST-REC ROWS
WITH SHIP-QTY > 0
RELATED BY SLMN-ID TO CAI-ORDERS-REC
SET NET-COST (8.2) = CAI-SLSHST-REC SHIP-QTY * UNIT-PRICE
SET DISCOUNT (5.2) = NET-COST * DISC-PCT
SORT CAI-ORDERS-REC BY (ORD-ID) AND SHIP-ID
PRINT FROM CAI-ORDERS-REC ORD-ID
SHIP-ID
FROM CAI-SLSHST-REC ITM-ID
SHIP-QTY
UNIT-PRICE
NET-COST
DISCOUNT
Sample
The following screen illustrates the effect when the SUPPRESS DUPLICATE COLUMNS option is set to YES. The value in the control break is presented only one time and the report column remains blank until the value in the control break changes.
Results with Suppress Duplicate Columns Set to YES
=> 01/02/2010 CA Dataquery PAGE 1 16:59:12 DETAIL ORD-ID SHIP-ID ITM-ID SHIP-QTY UNIT-PRICE NET-COST DISCOUNT ------ ------- ---------- -------- ---------- ------------- --------- 08811 00001 O50006 0000203 00014.75 00002994.25 00010.00 00002 O70001 0000002 00010.00 00000020.00 00002.33 09012 00007 C00006 0000100 00004.00 00000400.00 00012.82 00008 900006 0000100 00006.00 00000600.00 00002.82 00007 070006 0000100 00010.00 00001000.00 00010.00 09013 00008 O50006 0000112 00100.00 00001112.00 00002.01 09014 00008 O50006 0000002 00100.00 00000200.00 00002.09 -------------------------------- LAST PAGE --------------------------------- <PF1> HELP <PF2> RETURN <PF3> TOTALS ONLY <PF4> DETAIL <PF5> NO TOTALS <PF6> STATS <PF7> BACKWARD <PF8> FORWARD <PF9> NOT USED <PF10> NOT USED <PF11> LEFT <PF12> RIGHT
Sample
The following query illustrates the use of control breaks to present subtotals for columns designated as accumulation columns in the PRINT statement. The accumulation column in this example is NET-COST and it is enclosed in parentheses in the PRINT statement.
FIND 10 CAI-SLSHST-REC ROWS
WITH SHIP-QTY > 0
RELATED BY SLMN-ID TO CAI-ORDERS-REC
SET NET-COST (8.2) = CAI-SLSHST-REC SHIP-QTY * UNIT-PRICE
SET DISCOUNT (5.2) = NET-COST * DISC-PCT
SORT CAI-ORDERS-REC BY (ORD-ID) AND SHIP-ID
PRINT FROM CAI-ORDERS-REC ORD-ID
SHIP-ID
FROM CAI-SLSHST-REC ITM-ID
SHIP-QTY
UNIT-PRICE
(NET-COST)
DISCOUNT
Output
When the preceding query is executed, the screen displays a subtotal for NET-COST each time the value of ORD-ID changes. The following screen shows the results of the query.
=> 01/02/2010 CA Dataquery PAGE 1A 17:03:11 DETAIL ORD-ID SHIP-ID ITM-ID SHIP-QTY UNIT-PRICE NET-COST DISCOUNT ------ ------- ---------- -------- ---------- ------------- --------- 08811 00001 O50006 0000203 00014.75 00002994.25 08982.75 00002 O50006 0000010 00014.75 00000147.50 00002.75 TOTAL ORD-ID 08811 * 00003141.75 09312 00012 O50006 0000100 00014.75 00001475.00 04432.75 00015 O90001 0000010 00006.00 00000060.00 00012.75 TOTAL ORD-ID 09312 * 00001535.00 * GRAND TOTAL -------------------------------- LAST PAGE --------------------------------- <PF1> HELP <PF2> RETURN <PF3> TOTALS ONLY <PF4> DETAIL <PF5> NO TOTALS <PF6> STATS <PF7> BACKWARD <PF8> FORWARD <PF9> NOT USED <PF10> NOT USED <PF11> LEFT <PF12> RIGHT
After the word TOTAL, the name of the control break (ORD-ID) appears, followed by the value in the column. An asterisk precedes the accumulated value for the subtotal.
The last line of output presented is a total for the accumulated columns. The total is designated by * GRAND TOTAL and an asterisk preceding the accumulated value.
|
Copyright © 2014 CA.
All rights reserved.
|
|