Advanced SQL*Plus Reporting Features Techniques
This is the final section of Chapter 7. “Using Advanced SQL Techniques and SQL*Plus Reporting Features,” discusses more advanced reporting features of SQL*Plus: creating a cross-tabular report, using substitution variables from the operating-system command line along with a pre-formatted output result, and creating a master/detail report.
Cross-tabular reports are easy to do with a mix of SQL and SQL*Plus formatting commands. Listing 7.2 shows the CH3_XREF.SQL SQL*Plus script file and Listing 7.3 shows the output cross-tabular report from this script file. Notice again that there is a command line field that is passed in and appears in the heading. In this case, it is a report date. Notice the new format of the TTITLE at lines 28 and 29 (the - at the end of line 28 is a line-continuation character). The column format commands (lines 15 through 20) control the appearance of the columns. The DECODE lines 32 through 36 control which salary gets added to which bucket. Notice that the substr is only looking at the first column of job. This is simply because all the jobs are unique by the first position of the field. The combination of compute commands (lines 22 through 27), the SUM statements in the query (lines 32 through 37), and the Group By clause in the query (line 39) give the report output the appearance of a cross-tabular report.
Listing 7.2. CH3_XREF.SQL Script
1: rem 2: rem CH7_XREF.SQL - Creates a Cross Reference Matrix Report 3: rem 4: rem Oracle9i by Example 5: rem by Dan Hotka 6: rem Que Publications June 2001 7: rem All Rights Reserved 8: rem 9: define RPT_DATE = &1 10: SET FEEDBACK OFF 11: SET VERIFY OFF 12: SET TERMOUT OFF 13: SET ECHO OFF 14: SET LINESIZE 60 15: COLUMN CLERK format 999999 heading 'Clerk' 16: COLUMN MANAGER format 999999 heading 'Manager' 17: COLUMN SALESMAN format 999999 heading 'Salesman' 18: COLUMN ANALYST format 999999 heading 'Analyst' 19: COLUMN PRESIDENT format 999999 heading 'President' 20: COLUMN TOTALS format 999999 heading 'Totals' 21: BREAK ON REPORT SKIP 2 22: COMPUTE SUM OF CLERK on report 23: COMPUTE SUM OF ANALYST on report 24: COMPUTE SUM OF MANAGER on report 25: COMPUTE SUM OF SALESMAN on report 26: COMPUTE SUM OF PRESIDENT on report 27: COMPUTE SUM OF TOTALS on report 28: TTITLE left '&RPT_DATE' center 'Salary Cross-Tabular' RIGHT 'Page: ' format 999 - 29: SQL.PNO skip CENTER ' by Job/Department ' 30: SPOOL CH3_XREF.OUT 31: SELECT deptno, 32: SUM(DECODE(SUBSTR(job,1,1),'C',sal,0)) CLERK, 33: SUM(DECODE(SUBSTR(job,1,1),'A',sal,0)) ANALYST, 34: SUM(DECODE(SUBSTR(job,1,1),'M',sal,0)) MANAGER, 35: SUM(DECODE(SUBSTR(job,1,1),'S',sal,0)) SALESMAN, 36: SUM(DECODE(SUBSTR(job,1,1),'P',sal,0)) PRESIDENT, 37: SUM(sal) TOTALS 38: FROM emp 39: GROUP BY deptno 40: / 41: SPOOL OFF 42: EXIT |
Listing 7.3. Output Cross-Matrix Report from Listing 7.2
06/14/2001 Salary Cross-Tabular Page: 1
by Job/Department
DEPTNO Clerk Analyst Manager Salesman President Totals
--------- ------------- ------- ------------------ -------
10 1300 0 2450 0 5000 8750
20 1900 6000 2975 0 0 10875
30 950 0 2850 5600 0 9400
-- -- -- - -- -- -- - -- -- -- - -- -- -- -- -- -- -- -- - -- -- -- -
sum 4150 6000 8275 5600 5000 29025
|
Listing 7.4 illustrates how to load SQL*Plus define variables with data from a table. This technique is useful any time there is a need to create an output file (be it in a report format, SQL*Plus, or operating-system command-language scripts, and so on) that is based on data from inside a table. The importance of this concept is that these variables can be loaded from the Oracle database and their values referenced again in other SQL queries. The example is simple but the technique is powerful.
The important technique to observe in Listing 7.4 is the COLUMN commands (lines 20 and 21) and the select statement that loads the DNAME column (lines 23 through 25). Notice that the COLUMN name is referenced in the SQL statement but the NEW_VALUE name is referenced in the SQL statement (lines 28 and 29) that uses the contents of the variable. Also notice that there is one line of output for each combination of SELECT... FROM dual lines 28 and 29, 31 and 32, and 34 and 35. Also notice line 15, which is setting PAGESIZE to 0. This will cause no heading breaks, or even the blank line that appears even if SET HEADING OFF is used (line 16). This technique can be used to create SQL*Loader control files from information stored in the USER_TAB_COLUMNS or CREATE TABLE statements from the same USER_TAB_ COLUMNS, fixed formatted reports, and so on. See Listing 7.5 for the output from Listing 7.4.
Listing 7.4. CH7_LVAR.SQL Script
1: rem 2: CH7_LVAR.SQL - Demonstrates how to load variables from the contents 3: rem of a table.w to load variables from the contents 4: rem Oracle9i by Example 5: rem by Dan Hotka 6: rem Que Publications June 2001 7: rem All Rights Reserved 8: rem 9: 10: SET FEEDBACK OFF 11: SET VERIFY OFF 12: SET TERMOUT OFF 13: SET ECHO OFF 14: SET LINESIZE 60 15: SET PAGESIZE 0 16: SET HEADING OFF 17: 18: define DEPTNO = &1 19: 20: column DNAME new_value VAR_DNAME noprint 21: column LOC new_value VAR_LOC noprint 22: 23: SELECT dname, loc 24: FROM dept 25: WHERE deptno = &DEPTNO 26: / 27: spool CH3_LVAR.TXT 28: select 'This Department ' || ' &VAR_DNAME' || ' was loaded into a variable' 29: FROM dual 30: / 31: select 'from a database table that returned 1 row. This technique ' 32: FROM dual 33: / 34: select 'can be used to build any fixed formatted report, code syntax, etc.' 35: FROM dual 36: / 37: SPOOL OFF 38: EXIT |
Listing 7.5. Output from CH7_LVAR.SQL in Listing 7.4
This Department ACCOUNTING was loaded into a variable from a database table that returned 1 row. This technique can be used to build any fixed formatted report, code syntax, and so on. |
TIP
Use the column command with the NEW_VALUE option to load variables from Oracle9i tables to use in other SQL queries in the SQL*Plus script.
The final example in this chapter will make use of the UNION operator to create a master/detail type report.
Listing 7.6 creates a Master/Detail SQL*Plus Report by utilizing the SQL UNION command. In this example, there are six distinct separate types of lines to be printed: the Department line (line 27), a line of dashes under the department name (line 30), the employee detail line (line 33), a line of dashes under the detail total (line 36), a total line (lines 39 through 42), and a blank line between the groups (line 44). There are six separate queries that have their output merged and sorted together by the SQL JOIN statement (see lines 29, 32, 35, 38, and 43). When using JOIN to merge the output of two or more queries, the output result set MUST have the same number of columns and column types.
The headings are turned off (line 19) because regular SQL*Plus column headings are not desired for this type of report. The first column of each query has an alias column name of DUMMY. This DUMMY column is used to sort the order of the six types of lines (denoted by each of the six queries). The DUMMY column's only role is to maintain the order of the lines within the major sort field (DEPTNO in this example), so the NOPRINT option is specified in line 23. The final ORDER BY (line 46) actually merges the result set lines together to form the report in Listing 7.7. Notice line 33 uses a TO_CHAR function to ensure the output from this query is indeed character mode for the UNION operator. Also notice that each of the queries returns three columns: DUMMY, DEPTNO, and a character string. The INLINE view or a subquery in the FROM clause at lines 40 through 44, which allows for the SUM function to occur for the totals at the deptno breaks. The BREAK ON command (line 24) is to eliminate the duplicate DEPTNOs from the output report.
Listing 7.6. CH7_MDET.SQL Script
1: rem 2: rem CH7_MDET.SQL - Demonstrates how to create a Master/Detail report 3: rem using the UNION operator. This technique is useful 4: rem when ever records/text from different tables nees 5: rem to appear in the same report. 6: rem 7: rem Oracle9i by Example 8: rem by Dan Hotka 9: rem Que Publications June 2001 10: rem All Rights Reserved 11: rem 12: 13: SET FEEDBACK OFF 14: SET VERIFY OFF 15: SET TERMOUT OFF 16: SET ECHO OFF 17: SET LINESIZE 60 18: SET PAGESIZE 55 19: SET HEADING OFF 20: 21: TTITLE 'Employee Detail | by Department' 22: 23: COLUMN DUMMY NOPRINT 24: BREAK ON deptno 25: 26: SPOOL CH3_MDET.OUT 27: SELECT 1 DUMMY, deptno,'Department: ' || dname 28: FROM dept 29: UNION 30: SELECT 2 DUMMY,deptno,'-- -- -- -- -- -- -- -- -- -- -' 31: FROM dept 32: UNION 33: SELECT 3 DUMMY,deptno, ename || ' ' || TO_CHAR(sal,'$999,999') 34: FROM emp 35: UNION 36: SELECT 4 DUMMY,deptno,' -- -- -- -- -- ' 37: FROM dept 38: UNION 39: SELECT 5 DUMMY,deptno,'Total: ' || TO_CHAR(sal,'$999,999') 40: FROM (SELECT deptno, SUM(sal) sal 41: FROM emp 42: GROUP BY deptno) 43: UNION 44: SELECT 6 DUMMY,deptno,' ' 45: FROM dept 46: ORDER BY 2,1,3 47: / 48: SPOOL OFF 49: EXIT |
Listing 7.7. Output from CH7_MDET.SQL in Listing 3.9
Thr Jun 14 page 1
Employee Detail
by Department
10 Department: ACCOUNTING
-- -- -- -- -- -- -- -- -- -- -
CLARK $2,450
KING $5,000
MILLER $1,300
-- -- -- -- --
Total: $8,750
20 Department: RESEARCH
-- -- -- -- -- -- -- -- -- -- -
ADAMS $1,100
FORD $3,000
JONES $2,975
SCOTT $3,000
SMITH $800
-- -- -- -- --
Total: $10,875
30 Department: SALES
-- -- -- -- -- -- -- -- -- -- -
ALLEN $1,600
BLAKE $2,850
JAMES $950
MARTIN $1,250
TURNER $1,500
WARD $1,250
-- -- -- -- --
Total: $9,400
|