Re: Master - Detail Report From SQLPLUS

In article <01bc3609$6a62d9c0$65efb4cc_at_tiplady.specialtysystems.com>, "Tom Tiplady" <ttiplady_at_specialtysystems.com> wrote:
>Is there an easy way to construct a master detail report in SQLPLUS.
>There is one master table and two child tables.
>Report would look like
>
>Master1 XXXXXXXXX
> Detail 1 XXXXXXXXX XXXXXXXXXXX
> Detail 1 XXXXXXXXX XXXXXXXXXXX
> Detail 2 XXXXXXXXX XXXXXXXXXXX
> Detail 2 XXXXXXXXX XXXXXXXXXXX
>
>Master1 XXXXXXXXXX
> Detail 1 XXXXXXXXX XXXXXXXXXXX
> Detail 1 XXXXXXXXX XXXXXXXXXXX
> Detail 2 XXXXXXXXX XXXXXXXXXXX
>
>

SELECT dept, empname, salary FROM departments, employees WHERE department.ID = employees.deptno
GROUP BY dept;

That is the SQL statement (or variant) to get you started.

Formatting would then be done with various SQL*Plus commands, including SET COLUMN, BREAK ON, etc.

You may or may not be able to get exactly the report format you're showing above, but you *will* be able to get very close.

The SQL*Plus User's Guide has lots of helpful examples on how to do this.

>Is this better suited for PL/SQL?
>I also need output to go to an ASCII file.

Before executing your SQL, add a command:

SPOOL <pathname>/<textfilename>

And, after the SQL, add SPOOL OFF.

Good luck.

Loren

--
Loren Davidson      
loren_at_wombat.net         http://www.batnet.com/beauty/
"If war is the violent resolution of conflict, then peace is not the absence of conflict, but rather, the ability to resolve conflict without violence."
    -- C.T. Butler, "A Guide to Formal Consensus", http://www.ic.org/pnp/ocac/