Supress Repeating Values (Column breaks)

SQL*Plus provides several formatting options that improve the readability of your reports. In this lesson, you will learn how to prevent a value from being repeated in successive rows of a report and how to insert blank lines or a page break between groups of records. In the earlier lesson on generating ad-hoc reports, the sample report output looked like this:

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

In this example, because the report is sorted by owner and then by object type, the values in those columns tend to repeat from one row to the next. You can suppress these repeating values, so that they are printed only once, by adding either of the following commands to the report:

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

The keyword NODUPLICATES tells SQL*Plus to print the column's value only when it changes. NODUPLICATES is assumed anytime you issue a BREAK command, so in practice most script writers will leave it off to save themselves some typing. The example here shows both methods, and they are equivalent. The column order in the BREAK command should match that of the query's ORDER BY clause. This becomes especially important when you add page and line breaks to your report.

The CLEAR command is used to clear settings for the BREAK and COLUMN commands. Settings for these commands remain even after a report has been finished. For example, if your next query contains a column or alias named "title", the 999.99 format is used because this setting was added in the previous script. Therefore, any time you use the COLUMN or BREAK commands in a script, you should always clear the settings at the end of the script so that they don’t affect subsequent reports. Figure 5-9 shows the syntax of the CLEAR command.

CLEAR COLUMN|BREAK

FIGURE 5-9.1: Syntax of the CLEAR command
Figure 5-9.2 shows adding the CLEAR command at the end of the previous script to clear the COLUMN and BREAK settings.
Notice that the CLEAR command is issued twice:

1. once for the BREAK command's settings and
2. once for the COLUMN command's settings.

After adding the CLEAR command, any settings used for the report are cleared automatically after the report has been displayed.

The following scipt Figure 5-9.2 contains a SQL script with formatting commands typically used in SQL*Plus or similar Oracle Database tools.
Here's the printed text from the image:

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 BREAK
CLEAR COLUMN

Figure 5-9.2
Description of the Script in Figure 5-9.2:

1. Formatting Commands:
   • `SET LINESIZE 30` and `SET PAGESIZE 25` set the width of the line and the number of lines per page, respectively, which affects the output format when viewing results.
   • `TTITLE` and `BTITLE` set the title and bottom title of the report. `TTITLE` centers the text 'Amount Due Per Order' and skips two lines before it appears on the report. `BTITLE` includes the 'Run by:' label followed by the user's name in a fixed-width field of 5 characters (`FORMAT A5`).
2. Column Formatting: `COLUMN total FORMAT 999.99` sets the format for the `total` column to display two decimal places, allowing values up to 999.99.
3. Breaking and Sorting: `BREAK ON customer#` introduces a break in the report output whenever the value of `customer#` changes, making it easier to read grouped data.
4. SQL Query:
   • The `SELECT` statement is used to retrieve the customer number, order number, and the sum of `paidEach*quantity` as `total` from a join of the `orders` and `orderitems` tables, using `order#` as the joining condition.
   • It filters records for `customer#` less than 1007 and groups the results by `customer#` and `order#`. The results are also ordered by these columns, which ensures that the report is well-organized.
5. Cleanup Commands: `CLEAR BREAK` and `CLEAR COLUMN` are used to clear settings like breaks and column formats that were set earlier in the script. This helps in preventing these settings from affecting subsequent queries or reports run in the same session.