Suppressing Repeating Column Values with BREAK

SQL*Plus offers several formatting options that improve report readability, and this lesson covers two related ones: preventing a value from repeating on every row of a report, and inserting blank lines or a page break between groups of related rows. Recall the DBA_OBJECTS report from Lesson 2:

OWNER     OBJECT_TYP OBJECT_NAME
--------- ---------- ---------------
DBSNMP    SYNONYM    DBA_DATA_FILES
DBSNMP    SYNONYM    DBA_FREE_SPACE
DBSNMP    SYNONYM    DBA_SEGMENTS
DBSNMP    SYNONYM    DBA_TABLESPACES
OUTLN     INDEX      OL$HNT_NUM
OUTLN     INDEX      OL$NAME
OUTLN     INDEX      OL$SIGNATURE
OUTLN     TABLE      OL$
OUTLN     TABLE      OL$HINTS
PUBLIC    SYNONYM    ALL_ALL_TABLES

Because the report is sorted by owner and then by object type, both columns repeat their value from one row to the next, sometimes for several rows in a row. That repetition is exactly what BREAK exists to clean up.

You can suppress these repeating values, so each one prints only once per group, with either of the following equivalent commands:

BREAK ON owner NODUPLICATES ON object_type NODUPLICATES
BREAK ON owner ON object_type

The keyword NODUPLICATES tells SQL*Plus to print a column's value only when it changes from the row above. It is worth knowing that NODUPLICATES is assumed automatically the moment you issue any BREAK command at all, which is exactly why most script writers leave it off entirely, as the second line above does; both lines produce identical behavior.

One rule matters more than it might seem at first: the column order inside your BREAK command should match the column order in the query's ORDER BY clause. This is not just a style preference. If a BREAK column does not correspond to an ORDER BY column in the same way, breaks occur every single time that column's value changes, rather than only at the meaningful group boundaries you actually intended, since SQL*Plus has no way to group rows it was never told were sorted together in the first place.

Settings established by BREAK and COLUMN do not disappear once a report finishes; they persist for the rest of your SQL*Plus session. This becomes a real problem the moment you run a second, unrelated report afterward: if your next query happens to select a column or alias also named total, for instance, and a previous script set COLUMN total FORMAT 999.99, that formatting silently applies again, whether you wanted it or not. For this reason, any script that uses COLUMN or BREAK should clear those settings again before it finishes, so they cannot leak into whatever report runs next.

The command for this is CLEAR, and the officially documented form uses the plural keywords BREAKS and COLUMNS:

CLEAR BREAKS
CLEAR COLUMNS

Notice that CLEAR is issued twice here, once for each kind of setting: once to remove the BREAK definition, and once to reset every column's display attributes back to their defaults. Both are needed, since clearing one does not touch the other.

The following script pulls together several formatting commands covered across this module into one working report:

SET LINESIZE 30
SET PAGESIZE 25
TTITLE CENTER 'Amount Due Per Order' SKIP 2
BTITLE 'Run by:' SQL.USER FORMAT A5
COLUMN total FORMAT 999.99
BREAK ON customer#
SELECT customer#, order#, SUM(paidEach*quantity) total
FROM   orders JOIN orderitems USING(order#)
WHERE  customer# < 1007
GROUP BY customer#, order#
ORDER BY customer#, order#;
CLEAR BREAKS
CLEAR COLUMNS

Working through what each line actually does: SET LINESIZE 30 and SET PAGESIZE 25 control how wide each line is and how many lines fit on a page before SQL*Plus starts a new one. TTITLE CENTER 'Amount Due Per Order' SKIP 2 centers that text as a report title and leaves two blank lines beneath it before the report body begins. BTITLE 'Run by:' SQL.USER FORMAT A5 places a footer on every page showing who ran the report, using SQL.USER, a system-maintained variable that always holds the current username, formatted to a fixed width of five characters.

COLUMN total FORMAT 999.99 formats the total column to two decimal places. BREAK ON customer# introduces a break whenever the customer# value changes, which, combined with the query's own ORDER BY customer#, order#, correctly groups every order belonging to the same customer together, following exactly the matching rule covered earlier in this lesson.

The query itself joins orders and orderitems on order#, sums paidEach times quantity as total, filters to customers numbered below 1007, and groups and orders by customer# and order# so the results come back in a sensible, predictable sequence. Finally, CLEAR BREAKS and CLEAR COLUMNS reset both settings at the end, exactly as recommended above, so nothing from this script bleeds into whatever report you run next in the same session.