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.TXTExample 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 suppressedand 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.TXTUsing 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.TXTPerform 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.TXTExample 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.TXTUsing 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