Go4Expert

Go4Expert (http://www.go4expert.com/)
-   Oracle (http://www.go4expert.com/forums/oracle-forum/)
-   -   Equivalent of Hierarchy SQL in Oracle (http://www.go4expert.com/forums/equivalent-hierarchy-sql-oracle-t593/)

rchenna 17Feb2006 15:16

Equivalent of Hierarchy SQL in Oracle
 
Hi All,

We have some performance issues with 'CONNECT BY PRIOR' SQL statement and trying to find an alternate SQL which gives the same output.

Example:

ENum-----------------Mgr
Julie
Andrew---------------Julie
Mark-----------------Andrew
Matt-----------------Andrew
Wyatt----------------Julie
Jenny----------------Wyatt

SELECT enum, mgr FROM <table> START WITH enum IS NULL
CONNECT BY PRIOR enum = mgr;

Output:
-------
Julie
---Andrew
------Mark
------Matt
---Wyatt
------Jenny
---Joel

How do I get the same output without using CONNECT BY PRIOR command. I dont mind creating a new table that will keep all the possible combinations between enum and mgr columns.

Please provide your solution. I really appreciate it.

Thanks in advance,
Rao


--
Rao V Chenna

shabbir 17Feb2006 16:54

Re: Equivalent of Hierarchy SQL in Oracle
 
You have tried to post in Article section and it should be in the Queries and Expert comments section. I have moved to the required section and also deleted the duplicate times you tried to posts.

shabbir 17Feb2006 17:10

Re: Equivalent of Hierarchy SQL in Oracle
 
What you are looking for is a Manager and under whom each sub manager and sub-sub manger is which in turn results into recursion ouput and so the process becomes slow. If you have some hierarchy then you can use Vies to store the data. Something like your maximum ENum to Mgr relationship can be 5 or any n.


All times are GMT +5.5. The time now is 00:13.