Release 8.1.5
A66736-01
 Library |
 Product |
 Contents |
 Index |
| SQL*Plus User's Guide and Reference Release 8.1.5 A66736-01 |
|
Formatting Query Results, 3 of 5
When you use an ORDER BY clause in your SQL SELECT command, rows with the same value in the ordered column (or expression) are displayed together in your output. You can make this output more useful to the user by using the SQL*Plus BREAK and COMPUTE commands to create subsets of records and add space and/or summary lines after each subset.
The column you specify in a BREAK command is called a break column. By including the break column in your ORDER BY clause, you create meaningful subsets of records in your output. You can then add formatting to the subsets within the same BREAK command, and add a summary line (containing totals, averages, and so on) by specifying the break column in a COMPUTE command.
For example, the following query, without BREAK or COMPUTE commands,
SQL> SELECT DEPTNO, ENAME, SAL 2 FROM EMP 3 WHERE SAL < 2500 4 ORDER BY DEPTNO;
produces the following unformatted results:
DEPTNO ENAME SAL -------- ---------- --------- 10 CLARK 2450 10 MILLER 1300 20 SMITH 800 20 ADAMS 1100 30 ALLEN 1600 30 JAMES 950 30 TURNER 1500 30 WARD 1250 30 MARTIN 1250
To make this report more useful, you would use BREAK to establish DEPTNO as the break column. Through BREAK you could suppress duplicate values in DEPTNO and place blank lines or begin a new page between departments. You could use BREAK in conjunction with COMPUTE to calculate and print summary lines containing the total (and/or average, maximum, minimum, standard deviation, variance, or count of rows of) salary for each department and for all departments.
The BREAK command suppresses duplicate values by default in the column or expression you name. Thus, to suppress the duplicate values in a column specified in an ORDER BY clause, use the BREAK command in its simplest form:
BREAK ON break_column
|
Note: Whenever you specify a column or expression in a BREAK command, use an ORDER BY clause specifying the same column or expression. If you do not do this, the breaks may appear to occur randomly. |
To suppress the display of duplicate department numbers in the query results shown above, enter the following commands:
SQL> BREAK ON DEPTNO SQL> SELECT DEPTNO, ENAME, SAL 2 FROM EMP 3 WHERE SAL < 2500 4 ORDER BY DEPTNO;
SQL*Plus displays the following output:
DEPTNO ENAME SAL ---------- ----------- --------- 10 CLARK 2450 MILLER 1300 20 SMITH 800 ADAMS 1100 30 ALLEN 1600 JAMES 950 TURNER 1500 WARD 1250 MARTIN 1250
You can insert blank lines or begin a new page each time the value changes in the break column. To insert n blank lines, use the BREAK command in the following form:
BREAK ON break_column SKIP n
To skip a page, use the command in this form:
BREAK ON break_column SKIP PAGE
To place one blank line between departments, enter the following command:
SQL> BREAK ON DEPTNO SKIP 1
Now rerun the query:
SQL> /
SQL*Plus displays the results:
DEPTNO ENAME SAL ---------- ----------- --------- 10 CLARK 2450 MILLER 1300 20 SMITH 800 ADAMS 1100 30 ALLEN 1600 JAMES 950 TURNER 1500 WARD 1250 MARTIN 1250
You may wish to insert blank lines or a blank page after every row. To skip n lines after every row, use BREAK in the following form:
BREAK ON ROW SKIP n
To skip a page after every row, use
BREAK ON ROW SKIP PAGE
Suppose you have more than one column in your ORDER BY clause and wish to insert space when each column's value changes. Each BREAK command you enter replaces the previous one. Thus, if you want to use different spacing techniques in one report or insert space after the value changes in more than one ordered column, you must specify multiple columns and actions in a single BREAK command.
First, add another column to the current query:
SQL> L 1 SELECT DEPTNO, ENAME, SAL 2 FROM EMP 3 WHERE SAL < 2500 4* ORDER BY DEPTNO SQL> 1 SELECT DEPTNO, JOB, ENAME, SAL SQL> 4 ORDER BY DEPTNO, JOB
Now, to skip a page when the value of DEPTNO changes and one line when the value of JOB changes, enter the following command:
SQL> BREAK ON DEPTNO SKIP PAGE ON JOB SKIP 1
To show that SKIP PAGE has taken effect, create a TTITLE with a page number, enter
SQL> TTITLE COL 35 FORMAT 9 'Page:' SQL.PNO
Run the new query to see the results:
SQL> / Page: 1 DEPTNO JOB ENAME SAL ---------- --------- ---------- ---------- 10 CLERK MILLER 300 MANAGER CLARK 2450 Page: 2 DEPTNO JOB ENAME SAL ---------- --------- ---------- ---------- 20 CLERK SMITH 800 ADAMS 1100 Page: 3 DEPTNO JOB ENAME SAL ---------- --------- ---------- ---------- 30 CLERK JAMES 950 SALESMAN ALLEN 1600 TURNER 1500 WARD 1250 MARTIN 1250
You can list your current break definition by entering the BREAK command with no clauses:
BREAK
You can remove the current break definition by entering the CLEAR command with the BREAKS clause:
CLEAR BREAKS
You may wish to place the command CLEAR BREAKS at the beginning of every command file to ensure that previously entered BREAK commands will not affect queries you run in a given file.
If you organize the rows of a report into subsets with the BREAK command, you can perform various computations on the rows in each subset. You do this with the functions of the SQL*Plus COMPUTE command. Use the BREAK and COMPUTE commands together in the following forms:
BREAK ON break_column COMPUTE function LABEL label_name OF column column column ... ON break_column
You can include multiple break columns and actions, such as skipping lines in the BREAK command, as long as the column you name after ON in the COMPUTE command also appears after ON in the BREAK command. To include multiple break columns and actions in BREAK when using it in conjunction with COMPUTE, use these commands in the following forms:
BREAK ON break_column_1 SKIP PAGE ON break_column_2 SKIP 1 COMPUTE function LABEL label_name OF column column column ... ON break_column_2
The COMPUTE command has no effect without a corresponding BREAK command.
You can COMPUTE on NUMBER columns and, in certain cases, on all types of columns. For more information about the COMPUTE command, see the "Command Reference" in Chapter 8.
The following table lists compute functions and their effects
The function you specify in the COMPUTE command applies to all columns you enter after OFF and before ON. The computed values print on a separate line when the value of the ordered column changes.
Labels for ON REPORT and ON ROW computations appear in the first column; otherwise, they appear in the column specified in the ON clause.
You can change the compute label by using COMPUTE LABEL. If you do not define a label for the computed value, SQL*Plus prints the unabbreviated function keyword.
The compute label can be suppressed by using the NOPRINT option of the COLUMN command on the break column. See the COMPUTE command in Chapter 8 for more details.
To compute the total of SAL by department, first list the current BREAK definition:
SQL> BREAK break on DEPTNO skip 0 page nodup on JOB skip 1 nodup
Now enter the following COMPUTE command and run the current query:
SQL> COMPUTE SUM OF SAL ON DEPTNO SQL> /
SQL*Plus displays the following output:
DEPTNO JOB ENAME SAL ---------- --------- ---------- ---------- 10 CLERK MILLER 1300 MANAGER CLARK 2450 ********** ********* ---------- sum 3750 DEPTNO JOB ENAME SAL ---------- --------- ---------- ---------- 20 CLERK SMITH 800 ADAMS 1100 ********** ********* ---------- sum 1900 DEPTNO JOB ENAME SAL ---------- --------- ---------- ---------- 30 CLERK JAMES 950 SALESMAN ALLEN 1600 TURNER 1500 WARD 1250 MARTIN 1250 ********** ********* ---------- sum 6550
To compute the sum of salaries for departments 10 and 20 without printing the compute label:
SQL> COLUMN DUMMY NOPRINT SQL> COMPUTE SUM OF SAL ON DUMMY SQL> BREAK ON DUMMY SKIP 1 SQL> SELECT DEPTNO DUMMY, DEPTNO, ENAME, SAL 2 FROM EMP 3 WHERE DEPTNO <= 20 4 ORDER BY DEPTNO;
SQL*Plus displays the following output:
DEPTNO ENAME SAL ---------- ---------- ---------- 10 KING 5000 10 CLARK 2450 10 MILLER 1300 ---------- 8750 20 JONES 2975 20 FORD 3000 20 SMITH 800 20 SCOTT 3000 20 ADAMS 1100 ---------- 10875
To compute the salaries at the end of the report:
SQL> COLUMN DUMMY NOPRINT SQL> COMPUTE SUM OF SAL ON DUMMY SQL> BREAK ON DUMMY SQL> SELECT NULL DUMMY, DEPTNO, ENAME, SAL 2 FROM EMP 3 WHERE DEPTNO <= 20 4 ORDER BY DEPTNO;
SQL*Plus displays the following output:
DEPTNO ENAME SAL ---------- ---------- ---------- 10 KING 5000 10 CLARK 2450 10 MILLER 1300 20 JONES 2975 20 FORD 3000 20 SMITH 800 20 SCOTT 3000 20 ADAMS 1100 ---------- 19625
You can calculate and print summary lines based on all values in a column by using BREAK and COMPUTE in the following forms:
BREAK ON REPORT COMPUTE function LABEL label_name OF column column column ... ON REPORT
To calculate and print the grand total of salaries for all salesmen and change the compute label, first enter the following BREAK and COMPUTE commands:
SQL> BREAK ON REPORT SQL> COMPUTE SUM LABEL TOTAL OF SAL ON REPORT
Next, enter and run a new query:
SQL> SELECT ENAME, SAL 2 FROM EMP 3 WHERE JOB = 'SALESMAN';
SQL*Plus displays the results:
ENAME SAL ---------- -------- ALLEN 1600 WARD 1250 MARTIN 1250 TURNER 1500 ********** -------- TOTAL 5600
To print a grand total (or grand average, grand maximum, and so on) in addition to subtotals (or sub-averages, and so on), include a break column and an ON REPORT clause in your BREAK command. Then, enter one COMPUTE command for the break column and another to compute ON REPORT:
BREAK ON break_column ON REPORT COMPUTE function LABEL label_name OF column ON break_column COMPUTE function LABEL label_name OF column ON REPORT
You can compute and print the same type of summary value on different columns. To do so, enter a separate COMPUTE command for each column.
To print the total of salaries and commissions for all salesmen, first enter the following COMPUTE command:
SQL> COMPUTE SUM OF SAL COMM ON REPORT
You do not have to enter a BREAK command; the BREAK you entered in Example 4-13 is still in effect. Now, add COMM to the current query:
SQL> 1 SELECT ENAME, SAL, COMM
Finally, run the revised query to see the results:
SQL> / ENAME SAL COMM ---------- -------- ---------- ALLEN 1600 300 WARD 1250 500 MARTIN 1250 1400 TURNER 1500 0 ********** -------- ---------- sum 5600 2200
You can also print multiple summary lines on the same break column. To do so, include the function for each summary line in the COMPUTE command as follows:
COMPUTE function LABEL label_name function LABEL label_name function LABEL label_name ... OF column ON break_column
If you include multiple columns after OFF and before ON, COMPUTE calculates and prints values for each column you specify.
To compute the average and sum of salaries for the sales department, first enter the following BREAK and COMPUTE commands:
SQL> BREAK ON DEPTNO SQL> COMPUTE AVG SUM OF SAL ON DEPTNO
Now, enter and run the following query:
SQL> SELECT DEPTNO, ENAME, SAL 2 FROM EMP 3 WHERE DEPTNO = 30 4 ORDER BY DEPTNO, SAL;
SQL*Plus displays the results:
DEPTNO ENAME SAL ---------- ---------- ---------- 30 JAMES 950 WARD 1250 MARTIN 1250 TURNER 1500 ALLEN 1600 BLAKE 2850 ********** ---------- avg 1566.66667 sum 9400
You can list your current COMPUTE definitions by entering the COMPUTE command with no clauses:
COMPUTE
You can remove all the COMPUTE definitions by entering the CLEAR command with the COMPUTES clause.
To remove all COMPUTE definitions and the accompanying BREAK definition, enter the following commands:
SQL> CLEAR BREAKS breaks cleared SQL> CLEAR COMPUTES computes cleared
You may wish to place the commands CLEAR BREAKS and CLEAR COMPUTES at the beginning of every command file to ensure that previously entered BREAK and COMPUTE commands will not affect queries you run in a given file.
|
 Copyright © 1999 Oracle Corporation. All Rights Reserved. |
|