You should create a pure function that returns the
top parent. This would be a simple PL/SQL loop
that walks to the top of the tree for the given
Emp name. Performance might be less than brilliant
but that's the price you pay.
Once the function is in place -- call it TopParent() --
then you just
select ename , mgr , TopParent(ename), ...
from emp
where ...
You'll find that TopParent ( 'Jesse' ) = 'Sciarra'.
Simple.
-Tom
- "Jesse, Rich" <Rich.Jesse_at_qtiworld.com> wrote:
> 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).
Thomas B. Cox "Saepe in errore sed numquam in dubito"
tbcox23@yahoo.com
http://www.geocities.com/tbcox23/
"The whole aim of practical politics is to keep the
populace alarmed (and hence clamorous to be led to
safety) by menacing it with an endless series of
hobgoblins, all of them imaginary." --H.L. Mencken
Do You Yahoo!?
Great stuff seeking new owners in Yahoo! Auctions!
http://auctions.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Thomas B. Cox
INET: tbcox23_at_yahoo.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).
Received on Tue Jan 29 2002 - 16:07:55 CST
