DQL Statements › PRINT Statement › Totals Syntax Diagram › Specifying Multiple Control Breaks

Specifying Multiple Control Breaks

If you specify more than one control break column in the SORT statement, your output will include a PRINT column subtotal for each specified control break column. The following query illustrates the use of multiple control breaks to obtain multiple subtotals.

FIND ALL CAI-SLSHST-REC ROWS
    WITH SHIP-QTY > 100
  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
     CAI-SLSHST-REC BY (ITM-ID)
PRINT FROM CAI-ORDERS-REC ORD-ID
      FROM CAI-SLSHST-REC ITM-ID
                          SHIP-QTY
                          UNIT-PRICE
                          (NET-COST)
                          DISCOUNT

Output

The results of the preceding sample query appear on the following screen.

 =>
01/02/2010                  CA Dataquery                             PAGE 1
15:34:44                                                                  DETAIL
 
  ORD-ID  ITM-ID     SHIP-QTY  UNIT-PRICE       NET-COST   DISCOUNT
  ------  ---------  --------  ----------  -------------  ---------
   08001  O50006      0000203    00014.75    00002994.25   00010.00
 
    TOTAL ITM-ID 05006
                                   * 00002994.25
   08001  O60011      0000193    00010.00    00001930.00   00010.00
    TOTAL ITM-ID 060011
                                   * 00001930.OO
   08001  O60012      0000100    00020.00    00002000.00   00010.00
    TOTAL ITM-ID 060012
                                   * 00002000.00
TOTAL ORD-ID 08001
                                   * 00006924.25
* GRAND TOTAL
                                   * 00006924.25
 -------------------------------- 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

Explanation

Notice in the preceding sample screen that the line identifying the ITM-ID subtotal is indented. The indented line indicates that this is the secondary control break column and the subtotal for this control break is accumulated first. The line designating the ORD-ID subtotal is not indented. The subtotal line for ORD-ID is not indented since this column is the primary control break column. The first column enclosed in parentheses in a SORT statement is always the primary control break column. Both subtotal lines identify the column used for the control break and the value contained in that column at the time the subtotal is accumulated.