Re: way to implement sqlplus break on in sql

From: sybrandb <sybrandb_at_gmail.com>
Date: 24 May 2007 02:54:28 -0700
Message-ID: <1180000468.454497.268850@g4g2000hsf.googlegroups.com>

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

Received on Thu May 24 2007 - 04:54:28 CDT