SQL*Plus User's Guide and Reference Release 9.0.1 Part Number A88827-02 |
|
Formatting Query Results, 3 of 6
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,
SELECT DEPARTMENT_ID, LAST_NAME, SALARY FROM EMP_DETAILS_VIEW WHERE SALARY > 12000 ORDER BY DEPARTMENT_ID; DEPARTMENT_ID LAST_NAME SALARY ------------- ------------------------- ---------- 20 Hartstein 13000 80 Russell 14000 80 Partners 13500 90 King 24000 90 Kochhar 17000 90 De Haan 17000 6 rows selected.
To make this report more useful, you would use BREAK to establish DEPARTMENT_ID as the break column. Through BREAK you could suppress duplicate values in DEPARTMENT_ID 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_columnTo suppress the display of duplicate department numbers in the query results shown above, enter the following commands:
BREAK ON DEPARTMENT_ID;
For the following query (which is the current query stored in the buffer):
SELECT DEPARTMENT_ID, LAST_NAME, SALARY FROM EMP_DETAILS_VIEW WHERE SALARY > 12000 ORDER BY DEPARTMENT_ID; DEPARTMENT_ID LAST_NAME SALARY ------------- ------------------------- ---------- 20 Hartstein 13000 80 Russell 14000 Partners 13500 90 King 24000 Kochhar 17000 De Haan 17000 6 rows selected.
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:
BREAK ON DEPARTMENT_ID SKIP 1
Now rerun the query:
/ DEPARTMENT_ID LAST_NAME SALARY ------------- ------------------------- ---------- 20 Hartstein 13000 80 Russell 14000 Partners 13500 90 King 24000 Kochhar 17000 De Haan 17000 6 rows selected.
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 PAGESuppose 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, clear the buffer:
CLEAR BUFFER
Type the following:
SELECT DEPARTMENT_ID, JOB_ID, LAST_NAME, SALARY FROM EMP_DETAILS_VIEW WHERE SALARY>12000 ORDER BY DEPARTMENT_ID, JOB_ID;
Now, to skip a page when the value of changes and one line when the value of changes, enter the following command:
BREAK ON SKIP PAGE ON SKIP 1
To show that SKIP PAGE has taken effect, create a TTITLE with a page number:
TTITLE COL 35 FORMAT 9 'Page:' SQL.PNO
Run the new query to see the results:
Page: 1 DEPARTMENT_ID JOB_ID LAST_NAME SALARY ------------- ---------- ------------------------- ---------- 20 MK_MAN Hartstein 13000 Page: 2 DEPARTMENT_ID JOB_ID LAST_NAME SALARY ------------- ---------- ------------------------- ---------- 80 SA_MAN Russell 14000 Partners 13500 Page: 3 DEPARTMENT_ID JOB_ID LAST_NAME SALARY ------------- ---------- ------------------------- ---------- 90 AD_PRES King 24000 AD_VP Kochhar 17000 De Haan 17000 6 rows selected.
Before continuing, turn off the top title display without changing its definition:
TTITLE OFF
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 OF 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 SALARY by department, first list the current BREAK definition:
BREAK
which displays current BREAK definitions:
break on DEPARTMENT_ID page nodup on JOB_ID skip 1 nodup
Now enter the following COMPUTE command and run the current query:
COMPUTE SUM OF SALARY ON DEPARTMENT_ID / DEPARTMENT_ID JOB_ID LAST_NAME SALARY ------------- ---------- ------------------------- ---------- 20 MK_MAN Hartstein 13000 ************* ********** ---------- sum 13000 DEPARTMENT_ID JOB_ID LAST_NAME SALARY ------------- ---------- ------------------------- ---------- 80 SA_MAN Russell 14000 Partners 13500 ************* ********** ---------- sum 27500 DEPARTMENT_ID JOB_ID LAST_NAME SALARY ------------- ---------- ------------------------- ---------- 90 AD_PRES King 24000 AD_VP Kochhar 17000 De Haan 17000 ************* ********** ---------- sum 58000 6 rows selected.
To compute the sum of salaries for departments 10 and 20 without printing the compute label:
COLUMN DUMMY NOPRINT; COMPUTE SUM OF SALARY ON DUMMY; BREAK ON DUMMY SKIP 1; SELECT DEPARTMENT_ID DUMMY,DEPARTMENT_ID, LAST_NAME, SALARY FROM EMP_DETAILS_VIEW WHERE SALARY>12000 ORDER BY DEPARTMENT_ID; DEPARTMENT_ID LAST_NAME SALARY ------------- ------------------------- ---------- 20 Hartstein 13000 ---------- 13000 80 Russell 14000 80 Partners 13500 ---------- 27500 90 King 24000 90 Kochhar 17000 90 De Haan 17000 ---------- 58000
6 rows selected.To compute the salaries just at the end of the report:
COLUMN DUMMY NOPRINT; COMPUTE SUM OF SALARY ON DUMMY; BREAK ON DUMMY; SELECT NULL DUMMY,DEPARTMENT_ID, LAST_NAME, SALARY FROM EMP_DETAILS_VIEW WHERE SALARY>12000 ORDER BY DEPARTMENT_ID; DEPARTMENT_ID LAST_NAME SALARY ------------- ------------------------- ---------- 20 Hartstein 13000 80 Russell 14000 80 Partners 13500 90 King 24000 90 Kochhar 17000 90 De Haan 17000 ---------- 98500 6 rows selected.
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 sales people and change the compute label, first enter the following BREAK and COMPUTE commands:
BREAK ON REPORT COMPUTE SUM LABEL TOTAL OF SALARY ON REPORT
Next, enter and run a new query:
SELECT LAST_NAME, SALARY FROM EMP_DETAILS_VIEW WHERE JOB_ID='SA_MAN'; LAST_NAME SALARY ------------------------- ---------- Russell 14000 Partners 13500 Errazuriz 12000 Cambrault 11000 Zlotkey 10500 ---------- TOTAL 61000
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 sales people, first enter the following COMPUTE command:
COMPUTE SUM OF SALARY COMMISSION_PCT ON REPORT
You do not have to enter a BREAK command; the BREAK you entered in Example 4-13 is still in effect. Now, change the first line of the select query to include COMMISSION_PCT:
1 1* SELECT LAST_NAME, SALARY APPEND , COMMISSION_PCT;
Finally, run the revised query to see the results:
/ LAST_NAME SALARY COMMISSION_PCT ------------------------- ---------- -------------- Russell 14000 .4 Partners 13500 .3 Errazuriz 12000 .3 Cambrault 11000 .3 Zlotkey 10500 .2 ---------- -------------- sum 61000 1.5
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 OF 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:
BREAK ON DEPARTMENT_ID COMPUTE AVG SUM OF SALARY ON DEPARTMENT_ID
Now, enter and run the following query:
SELECT DEPARTMENT_ID, LAST_NAME, SALARY FROM EMP_DETAILS_VIEW WHERE DEPARTMENT_ID = 30 ORDER BY DEPARTMENT_ID, SALARY; DEPARTMENT_ID LAST_NAME SALARY ------------- ------------------------- ---------- 30 Colmenares 2500 Himuro 2600 Tobias 2800 Baida 2900 Khoo 3100 Raphaely 11000 ************* ---------- avg 4150 sum 24900 6 rows selected.
You can list your current COMPUTE definitions by entering the COMPUTE command with no clauses:
COMPUTE
To remove all COMPUTE definitions and the accompanying BREAK definition, enter the following commands:
CLEAR BREAKS breaks cleared 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 © 1996-2001, Oracle Corporation. All Rights Reserved. |
|