Mark D Powell wrote:
> 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 --
I would agree except I didn't see the word "BREAK" in anything the OP
wrote: Thus my original answer.
--
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Thu May 24 2007 - 19:12:45 CDT
