Listing 22.9 creates a Master/Detail SQL*Plus Report by utilizing the SQL UNION command. In this example, there are six separate types of lines to be printed: the Salesperson (line 4), a line of dashes under the Salesperson (line 7), the detail line (line 10), a line of dashes under the detail total (line 14), a total line (line 17), and a blank line (line 21). There are six separate queries that have their output merged and sorted together by the SQL JOIN statement (see lines 6, 9, 13, 16, 19, and 23). When using JOIN to merge the output of two or more queries, the output result set must have the same number of columns. The headings are turned off (line 2) 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 (SALES_REP_NO in this example), so the NOPRINT option is specified in line 3. Listing 22.10 shows the output of the Master/Detail SQL*Plus Report code.

Listing 22.9. Master/Detail SQL*Plus report code.

1:    ttitle ‘Sales Detail | by Sales Rep’

2:    set HEADINGS OFF

3:    column DUMMY NOPRINT

4:    select 1 DUMMY, SALES_REP_NO,‘Sales Person: ‘ || SALES_REP

5:    from sales

6:    UNION

7:    select 2 DUMMY,SALES_REP_NO,’--------------------’

8:    from sales

9:    UNION

10:   select 3 DUMMY,SALES_REP_NO, rpad(CAR_MAKE,4) || ‘    ‘ ||

11:         to_char(SALE_AMT,’$999,999.99’)

12:   from sales_detail

13:   UNION

14:   select 4 DUMMY,SALES_REP_NO,’         ----------’

15:   from sales

16:   UNION

17:   select 5 DUMMY,SALES_REP_NO,’Total:  ’ ||

18:         to_char(sum(TOTAL_SALES),’$999,999.99’))

19:   from sales

20:   UNION

21:   select 6 DUMMY,SALES_REP_NO,’          ‘

22:   from sales

23:   order by 2,1,3

24:   /

Listing 22.10. Output of Master/Detail SQL*Plus report code.

************************

      Thur Apr 2

       page    1

                                       Sales Detail

                                 by Sales Rep





Salesperson:  Elizabeth

-----------------------------

Chrysler    $3,000

Chrysler    $1,500

Nissan      $2,000

Nissan      $2,000

Nissan      $1,500

Toyota      $2,500

----------

Total:     $12,500



Salesperson:   Emily

-----------------------------

Ford        $1,000

Ford        $1,000

GM          $2,000

GM          $2,400

Nissan      $2,000

Nissan      $2,000

Toyota      $1,000

Toyota      $2,500

Toyota      $2,500

----------

Total:     $16,400

Salesperson:  Thomas

-----------------------------

Chrysler    $1,500

Ford        $1,000

Ford        $3,000

GM          $1,400

GM          $1,600

GM          $3,000

Nissan      $2,000

Toyota      $1,000

----------

Total:     $16,400

Listing 22.11 creates a SQL*Plus Report utilizing different columns from different tables using the SQL UNION command. In this example, there are three different tables (see lines 8, 11, and 14) but there are only three columns of output. The first query contains the column names (see lines 6 and 7). This is due to the way the JOIN operator works. The queries after the first query must follow the number of columns and the type of column (text or numeric) based on the column definitions of the first query. The BREAK command (line 4) causes the OBJECT_NAME to print once and creates the blank line between the groupings of records. Listing 22.12 shows the output of Listing 22.11.

---

Tip:  
The next example uses the JOIN feature to display output from two or more tables within the same report.

---

Listing 22.11. Multi table SQL*Plus report code.

1:    column OBJECT_TYPE      format a20        heading ‘Object’

2:    column OBJECT_NAME      format a8         heading ‘Name’

3:    column COMMENT    format a8         heading ‘Comments’

4:    break on OBJECT_TYPE skip 1

5:    ttitle ‘System Summary Report

6:    select ‘Program’ OBJECT_TYPE, program_name OBJECT_NAME,

7:          program_comments  COMMENT

8:         from program_table

9:    UNION

10:   select ‘Command Language’,cl_name, assoc_system

11:        from cl_table

12:   UNION

13:   select ‘Files’,file_name, ‘File Size = ‘ || file_size ||  ‘Bytes’

14:      from file_table

15:   /

Listing 22.12. Output of multi table SQL*Plus report code.

      Thr Apr 2                                       page    1

                                       System Summary Report





      Object                   Name             Comments

      --------------------    ----------  --------------------------------

      Programs          AM1         Algebra Test 1

                        AM2         Algebra Test 2

                        AM3         Algebra Test 3



      Command Language  CL1         AM1

                        CL2         AM2

                        CL3         AM3



      Files             AM1.TST     File Size = 1200 Bytes

                        AM2.TST     File Size = 3000 Bytes

                        AM3.TST     File Size = 2200 Bytes

Listings 22.13 (method 1) and 22.14 (method 2) produce the exact same output as seen in Listing 22.15. Both of these methods will produce reports with information in fixed or predefined positions. Both of these methods could be utilized to print information on a pre-printed form. These particular examples were designed to be started from inside another process such as SQL*Forms because the only input parameter is an Oracle ROWID used to read and process a single row from the database (lines 1 and 26 in Listing 22.13, lines 1 and 42 in Listing 22.14).

These examples utilize the concatenation feature of SQL (||) to blend text between database fields. Each column in the SQL statement represents an individual line in the report. Both examples have the standard column headings feature turned off (line 3 of Listing 22.13, line 4 of Listing 22.14). Both examples have a one-to-one relationship between a SQL column and a line of output. The methods differ in how the columns are formatted to create the individual lines.