Prev: Problems with Oracle Text (in Oracle 10g)
Next: Analytics question maybe - combine over lapping date rows
From: David on 27 Jun 2008 12:00
Hi

I have the following query against the table
create table employees (
id number,
manager_id number,
name varchar2(100));

select e.id,lpad(e.name,length(e.name)+(level-1)*2,'-')
from employees e
start with e.id in (select e2.id
from employees e2
where e2.manager_id is null
connect by prior e2.manager_id = e2.id)
connect by prior e.id = e.manager_id;

This returns a list of employees as a hierarchy, with the managers at
the top and employees below, ordered as the hierarchy is structured.

However, not all employees have a manager, and there are several top
level managers. I want to list the level 1 employees in alphabetical
order, without affecting the display of the hierarchy levels where
they exist beneath these.

Is this possible?

Many thanks

David
From: Maxim Demenko on 27 Jun 2008 19:45
David schrieb:
> Hi
>
> I have the following query against the table
> create table employees (
> id number,
> manager_id number,
> name varchar2(100));
>
> select e.id,lpad(e.name,length(e.name)+(level-1)*2,'-')
> from employees e
> start with e.id in (select e2.id
> from employees e2
> where e2.manager_id is null
> connect by prior e2.manager_id = e2.id)
> connect by prior e.id = e.manager_id;
>
> This returns a list of employees as a hierarchy, with the managers at
> the top and employees below, ordered as the hierarchy is structured.
>
> However, not all employees have a manager, and there are several top
> level managers. I want to list the level 1 employees in alphabetical
> order, without affecting the display of the hierarchy levels where
> they exist beneath these.
>
> Is this possible?
>
> Many thanks
>
> David

It is not quite clear, what are your requirements, maybe, it will be
easier to provide a solution, if you could post a sample with test data
and required output. According to your query, all employees without
manager will be automatically placed on top of your hierarchy (i.e. they
are manager regardless they have employees or not). I can't understand
as well the purpose of the subquery - in my opinion, more readable is
the equivalent:

select e.id,lpad(e.name,length(e.name)+(level-1)*2,'-')
from employees e
start with e.manager_id is null
connect by prior e.id = e.manager_id;

Best regards

Maxim
From: Robert Klemme on 28 Jun 2008 12:09
On 28.06.2008 01:45, Maxim Demenko wrote:
> David schrieb:
>> Hi
>>
>> I have the following query against the table
>> create table employees (
>> id number,
>> manager_id number,
>> name varchar2(100));
>>
>> select e.id,lpad(e.name,length(e.name)+(level-1)*2,'-')
>> from employees e
>> start with e.id in (select e2.id
>> from employees e2
>> where e2.manager_id is null
>> connect by prior e2.manager_id = e2.id)
>> connect by prior e.id = e.manager_id;
>>
>> This returns a list of employees as a hierarchy, with the managers at
>> the top and employees below, ordered as the hierarchy is structured.
>>
>> However, not all employees have a manager, and there are several top
>> level managers. I want to list the level 1 employees in alphabetical
>> order, without affecting the display of the hierarchy levels where
>> they exist beneath these.
>>
>> Is this possible?
>
> It is not quite clear, what are your requirements, maybe, it will be
> easier to provide a solution, if you could post a sample with test data
> and required output. According to your query, all employees without
> manager will be automatically placed on top of your hierarchy (i.e. they
> are manager regardless they have employees or not). I can't understand
> as well the purpose of the subquery - in my opinion, more readable is
> the equivalent:
>
> select e.id,lpad(e.name,length(e.name)+(level-1)*2,'-')
> from employees e
> start with e.manager_id is null
> connect by prior e.id = e.manager_id;

I believe he wants to sort persons on the same hierarchy level by their
names - something like this

select e.id,lpad(e.name,length(e.name)+(level-1)*2,'-')
from employees e
start with e.manager_id is null
connect by prior e.id = e.manager_id
order by level, name;

Kind regards

robert

From: David on 30 Jun 2008 05:32
On Jun 28, 5:09 pm, Robert Klemme <shortcut...(a)googlemail.com> wrote:
> On 28.06.2008 01:45, Maxim Demenko wrote:
>
>
>
> > David schrieb:
> >> Hi
>
> >> I have the following query against the table
> >> create table employees (
> >>   id number,
> >>   manager_id number,
> >>   name varchar2(100));
>
> >> select e.id,lpad(e.name,length(e.name)+(level-1)*2,'-')
> >> from   employees e
> >> start with e.id in (select e2.id
> >>                     from   employees e2
> >>                     where  e2.manager_id is null
> >>                     connect by prior e2.manager_id = e2.id)
> >> connect by prior e.id = e.manager_id;
>
> >> This returns a list of employees as a hierarchy, with the managers at
> >> the top and employees below, ordered as the hierarchy is structured.
>
> >> However, not all employees have a manager, and there are several top
> >> level managers.  I want to list the level 1 employees in alphabetical
> >> order, without affecting the display of the hierarchy levels where
> >> they exist beneath these.
>
> >> Is this possible?
>
> > It is not quite clear, what are your requirements, maybe, it will be
> > easier to provide a solution, if you could post a sample with test data
> > and required output. According to your query, all employees without
> > manager will be automatically placed on top of your hierarchy (i.e. they
> > are manager regardless they have employees or not). I can't understand
> > as well the purpose of the subquery - in my opinion, more readable is
> > the equivalent:
>
> > select e.id,lpad(e.name,length(e.name)+(level-1)*2,'-')
> > from   employees e
> > start with e.manager_id is null
> > connect by prior e.id = e.manager_id;
>
> I believe he wants to sort persons on the same hierarchy level by their
> names - something like this
>
> select e.id,lpad(e.name,length(e.name)+(level-1)*2,'-')
> from   employees e
> start with e.manager_id is null
> connect by prior e.id = e.manager_id
> order by level, name;
>
> Kind regards
>
>         robert

Hi

Thanks for the responses. I'd better describe what I'm trying to
achieve, and what actually happens, using Robert's select:

> select e.id,e.manager_id,lpad(e.name,length(e.name)+(level-1)*2,'-')
> from employees e
> start with e.manager_id is null
> connect by prior e.id = e.manager_id
> order by level, name;

Gives

ID MANAGER_ID LPAD(E.NAME,LENGTH(E.NAME)+(LEVEL-1)*2,'-')
596 Smith
7 Ince
604 Clark
606 604 --Dibble
610 606 ----Cuthbert
612 606 ----Grub
614 612 ------Smith
608 604 --Davidson
85 Brown
234 Jones
568 Williams
584 Gibson
84 584 --Blake
600 84 ----Donaldson

I'm aiming to get the top-level names in alphabetical order, but keep
the hierarchies intact:

ID MANAGER_ID LPAD(E.NAME,LENGTH(E.NAME)+(LEVEL-1)*2,'-')
85 Brown
604 Clark
606 604 --Dibble
610 606 ----Cuthbert
612 606 ----Grub
614 612 ------Smith
608 604 --Davidson
584 Gibson
84 584 --Blake
600 84 ----Donaldson
7 Ince
234 Jones
596 Smith
568 Williams

Regards

David
From: Maxim Demenko on 30 Jun 2008 08:06
On 30 Jun., 11:32, David <dknight.w...(a)googlemail.com> wrote:
> On Jun 28, 5:09 pm, Robert Klemme <shortcut...(a)googlemail.com> wrote:
>
>
>
> > On 28.06.2008 01:45, Maxim Demenko wrote:
>
> > > David schrieb:
> > >> Hi
>
> > >> I have the following query against the table
> > >> create table employees (
> > >> id number,
> > >> manager_id number,
> > >> name varchar2(100));
>
> > >> select e.id,lpad(e.name,length(e.name)+(level-1)*2,'-')
> > >> from employees e
> > >> start with e.id in (select e2.id
> > >> from employees e2
> > >> where e2.manager_id is null
> > >> connect by prior e2.manager_id = e2.id)
> > >> connect by prior e.id = e.manager_id;
>
> > >> This returns a list of employees as a hierarchy, with the managers at
> > >> the top and employees below, ordered as the hierarchy is structured.
>
> > >> However, not all employees have a manager, and there are several top
> > >> level managers. I want to list the level 1 employees in alphabetical
> > >> order, without affecting the display of the hierarchy levels where
> > >> they exist beneath these.
>
> > >> Is this possible?
>
> > > It is not quite clear, what are your requirements, maybe, it will be
> > > easier to provide a solution, if you could post a sample with test data
> > > and required output. According to your query, all employees without
> > > manager will be automatically placed on top of your hierarchy (i.e. they
> > > are manager regardless they have employees or not). I can't understand
> > > as well the purpose of the subquery - in my opinion, more readable is
> > > the equivalent:
>
> > > select e.id,lpad(e.name,length(e.name)+(level-1)*2,'-')
> > > from employees e
> > > start with e.manager_id is null
> > > connect by prior e.id = e.manager_id;
>
> > I believe he wants to sort persons on the same hierarchy level by their
> > names - something like this
>
> > select e.id,lpad(e.name,length(e.name)+(level-1)*2,'-')
> > from employees e
> > start with e.manager_id is null
> > connect by prior e.id = e.manager_id
> > order by level, name;
>
> > Kind regards
>
> > robert
>
> Hi
>
> Thanks for the responses. I'd better describe what I'm trying to
> achieve, and what actually happens, using Robert's select:
>
> > select e.id,e.manager_id,lpad(e.name,length(e.name)+(level-1)*2,'-')
> > from employees e
> > start with e.manager_id is null
> > connect by prior e.id = e.manager_id
> > order by level, name;
>
> Gives
>
> ID MANAGER_ID LPAD(E.NAME,LENGTH(E.NAME)+(LEVEL-1)*2,'-')
> 596 Smith
> 7 Ince
> 604 Clark
> 606 604 --Dibble
> 610 606 ----Cuthbert
> 612 606 ----Grub
> 614 612 ------Smith
> 608 604 --Davidson
> 85 Brown
> 234 Jones
> 568 Williams
> 584 Gibson
> 84 584 --Blake
> 600 84 ----Donaldson
>
> I'm aiming to get the top-level names in alphabetical order, but keep
> the hierarchies intact:
>
> ID MANAGER_ID LPAD(E.NAME,LENGTH(E.NAME)+(LEVEL-1)*2,'-')
> 85 Brown
> 604 Clark
> 606 604 --Dibble
> 610 606 ----Cuthbert
> 612 606 ----Grub
> 614 612 ------Smith
> 608 604 --Davidson
> 584 Gibson
> 84 584 --Blake
> 600 84 ----Donaldson
> 7 Ince
> 234 Jones
> 596 Smith
> 568 Williams
>
> Regards
>
> David

Would *order siblings by name* not suffice for your needs?
Best regards

Maxim
 |  Next  |  Last
Pages: 1 2
Prev: Problems with Oracle Text (in Oracle 10g)
Next: Analytics question maybe - combine over lapping date rows