Re: Tree walking ordering at top of tree
From: Robert Klemme <shortcutter_at_googlemail.com>
Date: Sat, 28 Jun 2008 18:09:09 +0200
Message-ID: <6cn615F3hdn3lU1@mid.individual.net>
>
> 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;
Date: Sat, 28 Jun 2008 18:09:09 +0200
Message-ID: <6cn615F3hdn3lU1@mid.individual.net>
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 Received on Sat Jun 28 2008 - 11:09:09 CDT