Use of Break by keyword in progress 4GL

Question

What is the exact use of break by keyword in Progress 4GL? I am not getting a clear idea from the keyword help of OpenEdge. What is the main difference between by and break by keywords.

Answer 1

The BREAK or BREAK BY keyword enables grouping in queries whereas BY only sorts the result. The SQL equivalence is GROUP BY.

BREAK BY enables the use of several keywords inside the resulting iteration:

FIRST-OF/LAST-OF

Returns a TRUE value if the current iteration of a DO, FOR EACH, or REPEAT . . . BREAK block is the first/last iteration for a new break group, and modifies all three block types

FIRST/LAST

Returns a TRUE value if the current iteration of a DO, FOR EACH, or REPEAT . . . BREAK block is the first/last iteration of that block.

There are also a number of aggregate functions you could use. See online help for AVERAGE, COUNT, MAXIMUM, MINIMUM, TOTAL, SUB-AVERAGE, SUB-COUNT, SUB-MAXIMUM, SUB-MINIMUM and SUB-TOTAL.

Lets say you have this table:

Amount | Id
-----------
     1 | 1
     2 | 1
    10 | 2
     3 | 2
    -1 | 3
     0 | 3

And some examples:

/* Sorting based on amount. */
FOR EACH table by amount:
  DISPLAY amount id.
END.

/* Will display */
Amount | Id
-----------
    -1 | 3
     0 | 3
     1 | 1
     2 | 1
     3 | 2
    10 | 2

/* BREAK example */
FOR EACH table BREAK BY id BY amount:
    IF FIRST-OF(id) THEN DO:
      DISPLAY "First of".
    END.
    DISPLAY amount id.
END.

/* Will display */
Amount | Id |
-----------------
     1 | 1  | First of
     2 | 1  |
     3 | 2  | First of
    10 | 2  | 
    -1 | 3  | First of
     0 | 3  |