1. We have moved from vBulletin to XenForo and you are viewing the site in the middle of the move. Though the functional aspect of everything is working fine, we are still working on other changes including the new design on Xenforo.
    Dismiss Notice

Equivalent of Hierarchy SQL in Oracle

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

  1. rchenna

    rchenna New Member

    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

    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

    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