Re: way to implement sqlplus break on in sql

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: 24 May 2007 16:24:44 -0700
Message-ID: <1180049084.340689.60220@q75g2000hsh.googlegroups.com>

On May 24, 12:19 pm, DA Morgan <damor..._at_psoug.org> wrote:
> sybrandb wrote:
> > On May 24, 1:54 am, DA Morgan <damor..._at_psoug.org> wrote:
> >> grasp06110 wrote:
> >>> Hi Everybody,
> >>> Is there a better way to implement the "break on" functionality of
> >>> sqlplus in sql than what is shown below?
> >>> Thanks,
> >>> John
> >>> select
> >>> (case
> >>> when depno = lag(depno) over (order by depno)
> >>> then null
> >>> else depno
> >>> end) depno,
> >>> name
> >>> from
> >>> emp
> >>> /*
> >>> create and populate emp table
> >>> drop table emp;
> >>> create table emp (
> >>> name varchar2(20),
> >>> depno number
> >>> );
> >>> insert into emp values (
> >>> 'FLECK',
> >>> 1
> >>> );
> >>> insert into emp values (
> >>> 'WOOTEN',
> >>> 1
> >>> );
> >>> insert into emp values (
> >>> 'FUTURE MAN',
> >>> 1
> >>> );
> >>> insert into emp values (
> >>> 'JACK',
> >>> 2
> >>> );
> >>> insert into emp values (
> >>> 'JORMA',
> >>> 2
> >>> );
> >>> */
> >>> select
> >>> (case
> >>> when depno = lag(depno) over (order by depno)
> >>> then null
> >>> else depno
> >>> end) depno,
> >>> name
> >>> from
> >>> emp
> >> Show where?
> >> --
> >> Daniel A. Morgan
> >> University of Washington
> >> damor..._at_x.washington.edu
> >> (replace x with u to respond)
> >> Puget Sound Oracle Users Groupwww.psoug.org-Hide quoted text -

>

> >> - Show quoted text -

>


> > Here....

> > select

> >   (case

> >     when depno = lag(depno) over (order by depno)

> >         then null

> >         else depno

> >         end) depno,

> >   name

> > from
> > emp

>

> > Time to get spectacles, Daniel? Or just more coffee ? ;)

>


> > --

> > Sybrand Bakker
> > Senior Oracle DBA

>

> I'm wearing them. ;-)

>

> Go to the top of this email and the OP wrote "break on." Search for the
> word "break" in the body of the text? If it is there I still can't find
> it and neither can the 'find' functionality.
> --
> Daniel A. Morgan
> University of Washington
> damor..._at_x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>
> - Show quoted text -

Unfortunately I am not in front of my work terminal but I believe that the OP is referring to the SQLPlus break command which can be used to supress printing repeating column values. I changed depno to deptno and name to ename and ran the SQL against the old Oracle EMP table. As expected the posted SQL only displays the department number once per department along with the employee name, I was interrupted before I had time to verify that the ename returned is in fact in the associated department though I expect it is.

• from an example I keep -- rem ttitle off btitle off clear columns rem break on report skip 0 nodup - on job skip 1 nodup compute sum of sal on job report select ename, job, sal from emp order by job, sal /

{ text removed }
ENAME JOB SAL
---------- --------- ----------

WARD       SALESMAN        1250
MARTIN                               1250
TURNER                              1500
ALLEN                                1600
                ********* ----------
             sum             5600

                            ----------
sum                       29025

14 rows selected.

HTH -- Mark D Powell -- Received on Thu May 24 2007 - 18:24:44 CDT