Previous | Table of Contents | Next

Page 91

Defining Page Breaks

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.

Defining Computes

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

Previous | Table of Contents | Next