Page 91
When a listing or report is created, some columns may have the same value repeated row after row. This often occurs when records from a base table are combined with records from a related table. The base table and the related table generally have a one-to-many relationship. When the records from the tables are combined, the primary key value from the base table is repeated for each of the matching records in the related table. Listing 4.5 illustrates a query of the employees in the `POL' department. The value `POL' repeats in each row.
Listing 4.5L_04_05.TXT—Example of Repeating Values
SQL> ttitle off
SQL> btitle off
SQL> select fk_department, last_name, first_name
2 from employee
3 where fk_department = `POL';
LAST
FK_D NAME FIRST NAME
---- --------------- ---------------
POL JOHNSON LYNDON
POL JOHNSON ANDREW
POL CLINTON WILLIAM
POL NIXON RICHARD
POL KENNEDY JOHN
POL ROOSEVELT FRANKLIN
POL WILSON WOODROW
7 rows selected.
To increase the readability of the report, these repeating values are often suppressed—and printed only when the value changes. This is called a break. SQL*PLUS performs this function by using the break command. Listing 4.6 displays an example of this command.
Listing 4.6L_04_06.TXT—Using the Break Command
SQL> break on fk_department
SQL> select fk_department, last_name, first_name
2 from employee
3 where fk_department = `POL';
LAST
FK_D NAME FIRST NAME
---- --------------- ---------------
POL JOHNSON LYNDON
JOHNSON ANDREW
CLINTON WILLIAM
NIXON RICHARD
KENNEDY JOHN
ROOSEVELT FRANKLIN
WILSON WOODROW
7 records selected.
Page 92
A report may need multiple break columns for the preferred format. The break command accommodates this. The name of multiple columns can be placed in the break command. This will cause the value for each of the specified columns to be printed when they change. Each break colum name should be preceded by the keyword on. Multiple breaks allow each column to break independently of each other. Listing 4.7 demonstrates a multiple break command.
Listing 4.7L_04_07.TXT—Perform a Multiple Break Formatting Procedure
SQL> break on fk_department on last_name
SQL> select fk_department, last_name, first_name
2 from employee
3 order by fk_department, last_name;
FK_D LAST_NAME FIRST_NAME
---- --------------- ---------------
INT BUSH GEORGE
COOLIDGE CALVIN
EISENHOWER DWIGHT
FORD GERALD
ROOSEVELT THEODORE
TRUMAN HAROLD
POL CLINTON WILLIAM
JOHNSON LYNDON
ANDREW
KENNEDY JOHN
NIXON RICHARD
ROOSEVELT FRANKLIN
WILSON WOODROW
WEL ANTHONY SUSAN
CARTER JIMMY
HOOVER HERBERT
REAGAN RONALD
ROOSEVELT ELEANOR
TAFT WILLIAM
19 records selected.
As with the other format commands, the break command remains in the buffer and is used on all subsequent queries until you issue a new break command or until you clear the command by using the clear breaks command. Break points also prove useful at times to perform special format procedures. Table 4.4 lists these commands.
Table 4.4Break Settings
| Setting | Description |
| Nodup[licates] |
Suppresses the printing of the values for every row except the first one after the break. |
| Dup[licates] | Causes the value to be printed on each row. |
| On Report | Causes the report to break at the end of the report. |
Page 93
| Setting | Description |
| On Row | Causes the report to break on each row. |
| Page | Causes the query to print the next row after the break on a new page. |
| Skip [n] | Causes the report to skip [n] lines after the break. |
The break command can contain as many settings as needed. You can place settings after each break column. These setting work when that particular column breaks. The break command must be one continuous statement. By using the dash (-), you can continue it on a new line. Typing the command break with no settings causes the editor to display the current break settings. It is also important to order the columns with the order by clause in the same pattern as the break setting. A break command does not order data, and causes haphazard breaking without a matching order by clause. Listing 4.8 shows a break command that skips two lines at the secondary break and pages at the major break.
Listing 4.8L_04_08.TXT—Example of a Multiple Column Break Command
SQL> clear columns
columns cleared
SQL> btitle off
SQL> title off
unknown command "title off" - rest of line ignored.
SQL> break on fk_department page on last_name skip 2
SQL> select fk_department, last_name, first_name, wages
2 from employee
3 order by 1,2;
FK_D LAST_NAME FIRST_NAME WAGES
---- --------------- --------------- --------
INT BUSH GEORGE 14000
COOLIDGE CALVIN 9500
EISENHOWER DWIGHT 0
FORD GERALD 13000
ROOSEVELT THEODORE 8000
TRUMAN HAROLD 11000
FK_D LAST_NAME FIRST_NAME WAGES
---- --------------- -------------- ---------
continues
Page 94
Listing 4.8Continued
POL CLINTON WILLIAM 15000
JOHNSON LYNDON 12000
ANDREW 7500
KENNEDY JOHN 11500
NIXON RICHARD 12500
ROOSEVELT FRANKLIN 10400
WILSON WOODROW 9000
FK_D LAST_NAME FIRST_NAME WAGES
---- -------------- --------------- ---------
WEL ANTHONY SUSAN 7000
CARTER JIMMY 13000
HOOVER HERBERT 10000
REAGAN RONALD 13500
ROOSEVELT ELEANOR 0
TAFT WILLIAM 8500
19 rows selected.
It is useful to print subtotals or other calculations at the break points. To perform this
functionality, use the compute command in conjunction with the command. The syntax of the
command is: compute {functions} of {column names} on {break column
names}. The various functions follow the word
compute, the expressions or columns on which to perform the
computations
Page
95
follow the of, and the columns that determine when to make the calculations follow the word on. Table 4.5 contains functions that can be performed using compute.
Table 4.5Compute Functions
| Function | Description |
| Avg | Calculates the average of the not null values in the set |
| Count | Calculates the number of not null observations in the set |
| Maximum | Calculates the maximum value in the set |
| Minimum | Calculates the minimum value in the set |
| Number | Calculates the number of returned records, including nulls |
| Std | Calculates the standard deviation of the not null values in the set |
| Sum | Calculates the total of the not null values in the set |
| Variance | Calculates the variance of the not null values in the set |
Listing 4.9 illustrates a compute command. It computes the average and total wages per department. The command file causes the subtotals to print at the break point. A row of stars appears on the left side of the row indicating subtotals. Below the stars, the function names are listed on subsequent rows. One final word on the compute command: It remains in the buffer until you enter the clear computes command or another compute supersedes it.
Listing 4.9L_04_09.TXT—Using the Compute Command
SQL> ttitle off
SQL> btitle off
SQL> clear breaks
breaks cleared
SQL> clear columns
columns cleared
SQL> clear computes
computes cleared
SQL> break on fk_department skip 2
SQL> compute avg sum of wages on fk_department
SQL> select fk_department, last_name, wages
2 from employee
3 order by 1,2;
FK_D LAST_NAME WAGES
---- --------------- ---------
INT BUSH 14000
COOLIDGE 9500
EISENHOWER 0
FORD 13000