| Previous | Table of Contents | Next |
The remainder of this section will cover some common SQL*Plus report formatting features and SQL generation techniques.
This is a simple but common form of SQL*Plus formatting. The syntax in Listing 22.5 is in the form of a file that gets passed to SQL*Plus in the manner described previously, passing a command line parameter (&1 on line 1) and assigning it to the variable name ASSIGNED_ANALYST. The ASSIGNED_ANALYST variable is then used in the headings (see line 13) and again as part of the SQL query (see line 17). Lines 2, 3, 4, and 5 suspend all terminal output from the SQL*Plus environment. The && is utilized to denote substitution of an already defined variable. The report output (Listing 22.6) contains two breaks, one when the column APPLICATION_NAME changes (see line 9 of listing 22.5) and one at the end of the report (see line 10 of listing 22.5). Totals are also calculated for each of these breaks (see lines 11 and 12). The | in the TTITLE command (see line 13) moves the following text onto its own line. Line 14 will open an operating system file named ANALYST.LIS in the current operating system directory. The .LIS suffix is a SQL*Plus system default. The order by clause of the query on line 18 insures that the breaks occur in an orderly manner.
Listing 22.5. Simple SQL*Plus report code.
1: define ASSIGNED_ANALYST = &1 2: set FEEDBACK OFF 3: set VERIFY OFF 4: set TERMOUT OFF 5: set ECHO OFF 6: column APPLICATION_NAME format a12 heading ‘Application’ 7: column PROGRAM_NAME format a12 heading ‘Program’ 8: column PROGRAM_SIZE format 999999 heading ‘Program|Size’ 9: break on APPLICATION_NAME skip 2 10: break on report skip 2 11: compute sum of PROGRAM_SIZE on APPLICATION_NAME 12: compute sum of PROGRAM_SIZE on report 13: ttitle ‘Programs by Application | Assigned to: &&ASSIGNED_ANALYST’ 14: spool ANALYST 15: select APPLICATION_NAME,PROGRAM_NAME,nvl(PROGRAM_SIZE,0) 16: from APPLICATION_PROGRAMS 17: where ASSIGNED_NAME = ‘&&ASSIGNED_ANALYST’ 18: order by APPLICATION_NAME,PROGRAM_NAME 19: / 20: spool off 21: exit
Listing 22.6. Output of simple SQL*Plus report.
Tue Feb 1
page 1
Programs by Application
Assigned to: HOTKA
Program
Application Program Size
------------ ------------ ---------
COBOL CLAIMS 10156
HOMEOWN 22124
PREMIUMS 10345
---------
sum 42625
FORTRAN ALGEBRA 6892
MATH1 7210
SCIENCE1 10240
---------
sum 24342
sum 66967
This section will cover more practical reporting uses for SQL*Plus.
Listing 22.7 is a cross tabular SQL*Plus command file. This report passes a command line parameter (&1 on line 1) and assigns it to the variable name RPT_DATE. The RPT_DATE variable is then used in the headings (see line 20) and again as part of the SQL query (see line 31). Lines 2, 3, 4, and 5 suspend all terminal output from the SQL*Plus environment. The report will be created in the operating system-dependent file SALES.OUT (see Listing 22.8). Column formatting commands control the appearance of the columns (lines 6 through 12). The combination of compute commands (lines 14 through 19), the sum statements in the query (lines 24 through 29), and the group by clause in the query (line 32) give the report output the appearance of a cross tabular report.
Note:
I utilized a different TTITLE technique in Listing 22.7 (lines 20 and 21) from that of Listing 22.5 (line 13).
Listing 22.7. Cross tabular SQL*Plus report.
1: define RPT_DATE = &1 2: set FEEDBACK OFF 3: set VERIFY OFF 4: set TERMOUT OFF 5: set ECHO OFF 6: column SALES_REP format a12 heading ‘Sales|Person’ 7: column NISSAN format 999999 heading ‘Nissan’ 8: column TOYOTA format 999999 heading ‘Toyota’ 9: column GM format 999999 heading ‘GM’ 10: column FORD format 999999 heading ‘Ford’ 11: column CRYSLER format 999999 heading ‘Crysler’ 12: column TOTALS format 999999 heading ‘Totals’ 13: break on report skip 2 14: compute sum of NISSAN on report 15: compute sum of TOYOTA on report 16: compute sum of GM on report 17: compute sum of FORD on report 18: compute sum of CRYSLER on report 19: compute sum of TOTALS on report 20: ttitle left ‘&&IN_DATE’ center ‘Auto Sales’ RIGHT ‘Page: ‘ format 999 - 21: SQL.PNO skip CENTER ‘ by Sales Person ‘ 22: spool SALES.OUT 23: select SALES_REP, 24: sum(decode(CAR_TYPE,’N’,TOTAL_SALES,0)) NISSAN, 25: sum(decode(CAR_TYPE,’T’,TOTAL_SALES,0)) TOYOTA, 26: sum(decode(CAR_TYPE,’G’,TOTAL_SALES,0)) GM, 27: sum(decode(CAR_TYPE,’F’,TOTAL_SALES,0)) FORD, 28: sum(decode(CAR_TYPE,’C’,TOTAL_SALES,0)) CRYSLER , 29: sum(TOTAL_SALES) TOTALS 30: from CAR_SALES 31: where SALES_DATE <= to_date(‘&&RPT_DATE’) 32: group by SALES_REP 33: / 34: spool off 35: exit
Listing 22.8. Cross tabular SQL*Plus report output.
3-APR-98 Auto Sales
Page: 1
by Sales Person
Sales
Person Nissan Toyota GM Ford
Crysler Totals
-------- -------- ----- ------ ------- -------- --
------
Elizabeth 5500 2500 0 0
4500 12500
Emily 4000 6000 4400 2000
0 16400
Thomas 2000 1000 6000 4000
1500 14500
-------- ------ ------ ------- --------
-----
11500 9500 10400 6000
6000
43400
Note:
I will now only include the specific SQL*Plus commands necessary to produce the desired output.
| Previous | Table of Contents | Next |