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

From: Johnston, Tim <TJohnston_at_quallaby.com>
Date: Tue, 29 Jan 2002 11:07:32 -0800
Message-ID: <F001.003FF296.20020129103038@fatcity.com>

Would...

select emp, level from table_name connect by prior mgr=emp start with emp = 'Jesse';

work for you? It would look like...

Jesse           3
Urman           2

Sciarra 1

HTH
Tim

-----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: Johnston, Tim
INET: TJohnston_at_quallaby.com

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