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

From: Vadim Gorbounov <vgorbounov_at_simplyengineering.com>
Date: Tue, 29 Jan 2002 11:32:45 -0800
Message-ID: <F001.003FF48E.20020129111515@fatcity.com>

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

USA
--

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).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Vadim Gorbounov
INET: vgorbounov_at_simplyengineering.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------