Previous Table of Contents Next


SQL*Plus Reporting Techniques

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

Advanced Reporting Techniques

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