Equivalent of Hierarchy SQL in Oracle

Discussion in 'Oracle' started by rchenna, Feb 17, 2006.

  1. rchenna

    rchenna New Member

    Joined:
    Feb 17, 2006
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    0
    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
     
  2. shabbir

    shabbir Administrator Staff Member

    Joined:
    Jul 12, 2004
    Messages:
    15,375
    Likes Received:
    388
    Trophy Points:
    83
    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.
     
  3. shabbir

    shabbir Administrator Staff Member

    Joined:
    Jul 12, 2004
    Messages:
    15,375
    Likes Received:
    388
    Trophy Points:
    83
    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.
     

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice