RE: Top parent in START WITH...CONNECT BY

Sorry guys. As soon as I started reading the replies, I realized what I left out. <blush> The developer needs:

select e.emp from emp e
where e.mgr is null
connect by prior mgr = emp

start with emp in ('Jesse','King');
               ^^^^^^^^^^^^^^^^^^^^

...and still be able to tell the top-level (level 1) given any lower level.

Thx!

Rich Jesse                           System/Database Administrator
Rich.Jesse_at_qtiworld.com              Quad/Tech International, Sussex, WI USA
 

-----Original Message-----
Sent: Tuesday, January 29, 2002 1:15 PM
To: Multiple recipients of list ORACLE-L

Hi, Rich

This can be done:

select e.emp from emp e
where e.mgr is null
connect by prior mgr = emp
start with emp = 'Jesse';

HTH
Vadim

-----Original Message-----
Sent: Tuesday, January 29, 2002 12:20 PM To: Multiple recipients of list ORACLE-L

Hi,

On 8.1.7.2 (soon to be .3) on HP/UX 11.0, a developer needs to get the top
parent associated with a lower level row. Expanding on Oracle's EMP example:

EMP             MGR             LEVEL
King                            1
Greenberg       King            2
Faviet          Greenberg       3
Chen            Greenberg       3
Sciarra                 1
Urman           Sciarra 2
Popp            Urman           3
Jesse           Urman           3

...I need to know that the top-level manager for "Jesse" is "Sciarra".
In
our live data, however, we currently have 9 levels (engineering bill-of-materials) and there is no set maximum for levels.

Anyone have an idea how to return:
Jesse Urman Sciarra 3

...for this? The START WITH...CONNECT BY can only give the PRIOR
manager
and not the top level.

TIA!

Rich Jesse                           System/Database Administrator
Rich.Jesse_at_qtiworld.com              Quad/Tech International, Sussex, WI

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jesse, Rich
  INET: Rich.Jesse_at_qtiworld.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).