O'Reilly logo

Oracle SQL*Plus: The Definitive Guide, 2nd Edition by Jonathan Gennick

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Totals and Subtotals

SQL*Plus allows you to print totals and subtotals in a report. To do this, you use a combination of the BREAK command and one or more COMPUTE commands. This section continues with the master/detail report last shown in Chapter 5, in Example 5-8. It will show you how to modify that master/detail report so it totals the hours and dollars by project and by employee. You will see how to print grand totals for these columns at the end of the report.

To refresh your memory, Example 7-1 repeats the Project Hours and Dollars Detail report from Example 5-8.

Example 7-1. Master/detail report showing a breakdown of time billed to projects

SET ECHO OFF SET RECSEP OFF --Set up pagesize parameters SET NEWPAGE 0 SET PAGESIZE 55 --Set the linesize, which must match the number of equals signs used --for the ruling lines in the headers and footers. SET LINESIZE 66 --Set up page headings and footings TTITLE CENTER "The Fictional Company" SKIP 3 - LEFT "I.S. Department" - RIGHT "Project Hours and Dollars Detail" SKIP 1 - LEFT "========================================" - "==========================" - SKIP 2 "Employee: " FORMAT 9999 emp_id_var " " emp_name_var SKIP 3 BTITLE LEFT "========================================" - "==========================" - SKIP 1 - RIGHT "Page " FORMAT 999 SQL.PNO --Format the columns COLUMN employee_id NEW_VALUE emp_id_var NOPRINT COLUMN employee_name NEW_VALUE emp_name_var NOPRINT COLUMN project_id HEADING "Proj ID" FORMAT 9999 COLUMN project_name HEADING ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required