Re: SQL*Plus break header

From: Phil Bewig <pbewig_at_swbell.net>
Date: 17 Nov 2003 11:30:22 -0800
Message-ID: <455f7154.0311171130.2098cc1c_at_posting.google.com>

pbewig_at_swbell.net (Phil Bewig) wrote in message news:<455f7154.0311101302.310abb98_at_posting.google.com>...
> I am using SQL*Plus and ordinary SQL (no PL/SQL) to create a
> report. I would like to write a header line each time a field
> breaks:

I have a solution. It's only a little bit ugly....

Consider the sample database used in the PL/SQL manual, which has department and employee tables. The following SQP*Plus program prints a listing of employees by department:

• WARNING: UNTESTED CODE!

    TTITLE LEFT 'EMPLOYEES BY DEPARTMENT' SKIP

    COLUMN ORDINAL     NOPRINT
    COLUMN DEPT.DEPTNO FORMAT 99          HEADING 'DP'
    COLUMN ENAME       FORMAT A14         HEADING '   EMPLOYEE   '
    COLUMN HIREDATE    FORMAT A9          HEADING 'HIRE DATE'
    COLUMN SAL         FORMAT '99,999.99' HEADING '  SALARY '

    BREAK ON DEPT.DEPTNO SKIP     COMPUTE SUM OF SAL ON DEPT.DEPTNO     SELECT DISTINCT 1 AS ORDINAL,

              DEPT.DEPTNO,
              DNAME AS ENAME,
              NULL AS HIREDATE,
              NULL AS SAL
    FROM      EMP
    JOIN      DEPT
    ON        DEPT.DEPTNO = EMP.DEPTNO

    UNION ALL
    SELECT 2 AS ORDINAL,

              DEPT.DEPTNO,
              ENAME,
              HIREDATE,
              SAL
    FROM      EMP
    JOIN      DEPT
    ON        DEPT.DEPTNO = EMP.DEPTNO

    ORDER BY DEPT.DEPTNO, ORDINAL, ENAME The trick here is the dual use of the ENAME column to hold both the department name and employee name. The query uses a UNION ALL to create the break header as just another record in the output, with appropriate naming and sorting. Here is the output:

    EMPLOYEES BY DEPARTMENT     DP EMPLOYEE HIRE DATE SALARY

• -------------- --------- --------- 10 ACCOUNTING CLARK 09-JUN-81 2,450.00 KING 17-NOV-81 5,000.00 MILLER 23-JAN-82 1,300.00
  • --------- 8,750.00

    20 RESEARCH

       ADAMS          23-MAY-87  1,100.00
       FORD           03-DEC-81  3,000.00
       JONES          02-APR-81  2,975.00
       SCOTT          19-APR-87  3,000.00
       SMITH          17-DEC-80    800.00
    **                          ---------
                                10,875.00

    30 SALES
       ALLEN          20-FEB-81  1,600.00
       BLAKE          01-MAY-81  1,250.00
       JAMES          03-DEC-81    950.00
       MARTIN         28-SEP-81  1,250.00
       TURNER         08-SEP-81  1,500.00
       WARD           22-FEB-81  1,250.00
    **                          ---------
                                 7,800.00

This technique won't always work; there might not be a column available to be hijacked for dual use. But so far it has worked every place I needed it. It would be nicer if SQL*Plus provided a direct solution to break headers as it does report titles with embedded user variables.

Thanks to Frank, Pete and Sybrand for their suggestions.

Phil Received on Mon Nov 17 2003 - 20:30:22 CET