Using CREATE in DQL Mode › Creating the Sample Query › Step 5: Sorting the Data › Create a Control Break

Create a Control Break

To create a control break, follow these steps:

1. Press the Insert key.
2. Type an open parenthesis, (, before CUST-ID.
3. Type a close parenthesis, ), after CUST-ID.
4. Leave the cursor in position after the close parenthesis.

The EDITOR allows you to use the Insert key so that you can manually insert data into your query. Enclosing CUST-ID in parentheses indicates that this is to be a control break column. Each time a new value is encountered for CUST-ID, that is, a different customer, CA Dataquery can total certain numeric columns (to be designated in Step 6) when the query executes.

Completed Example

  =>

 --------------------------------------------------------------------------DQD10
 DATAQUERY:  EDITOR            CURRENT TABLE:  CAI-DETAIL-TBL
 -------------------------------------------------------------------------------
 NAME:          USER-SAMPLE                       TYPE: QUERY   STATUS: PRIVATE
 DESCRIPTION:   TOTAL ORDERS PER CUSTOMER -- AVERAGE PRICE
    ....+....1....+....2....+....3....+....4....+....5....+....6....+....7....+.
 .. ================================ T O P =====================================
 01  FIND ALL  CAI-DETAIL-TBL ROWS
 02       WITH SHIP-QTY GT 0
 03  SET SHIP-PRICE(7.2) = UNIT-PRICE * SHIP-QTY
 04  SORT BY (CUST-ID)
 .. ===========================  B O T T O M  ==================================






 -------------------------------------------------------------------------------
 <PF1> HELP        <PF2>  RETURN     <PF3> DISPLAY COLUMNS <PF4> DISPLAY KEYS
 <PF5> DISPLAY ALL <PF6>  LIST TABLES<PF7> BACKWARD        <PF8> FORWARD
 <PF9> TEMPLATE    <PF10> VALIDATE   <PF11> RIGHT/LEFT     <PF12>PROCESS MODE

Other Things You Could Do

You could use <PF9> TEMPLATE to display the format for the SET statement and insert the format on the CREATE MODE panel. Even though only one table is to be searched in the sample query, you can type the table name in the SORT statement.

If two or more columns were being used in the sort, then CUST-ID would be the primary sort column since 1 is typed in the space to the left of the column name. You can select the secondary sort column by typing a 2 in the space, the next with a 3, and so on for successive sort columns. Remember that the total length of all sort columns must be less than 100 characters.

Just as you can have multiple sort columns, you can also have multiple control break columns by enclosing each specific column in parentheses, up to a maximum of 10.

When you display the column DISPLAY panel, you can press <PF5> EXTENDED DEFN to display an extended definition of the columns. This display shows you information about the column, such as the type of data (character or numeric), the physical length of the column, and so forth.