Equivalent of Hierarchy SQL in Oracle
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Equivalent of Hierarchy SQL in Oracle

  1. #1
    Join Date
    Feb 2006
    Posts
    10

    Unhappy 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 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
  2. #2
    Join Date
    May 2002
    Posts
    2,645
    Do you have to have the rpad -- notation, or is the representation of parent-child-sibling sufficient?

  3. #3
    Join Date
    May 2002
    Posts
    2,645

    drop table org_chart;
    drop table names;

    -- 1Julie14

    -- 2andrew7 8wyatt11 12joel13
    -- 3mark4 5matt6 9jenny10


    create table org_chart
    (id number, left number, right number);
    insert into org_chart values
    (1, 1, 14);
    insert into org_chart values
    (2, 2, 7);
    insert into org_chart values
    (5, 3, 4);
    insert into org_chart values
    (6, 5, 6);
    insert into org_chart values
    (3, 8, 11);
    insert into org_chart values
    (7, 9, 10);
    insert into org_chart values
    (4, 12, 13);

    create table names
    (id number,
    name varchar2(10));
    insert into names values
    (1, 'Julie');
    insert into names values
    (2, 'Andrew');
    insert into names values
    (5, 'Mark');
    insert into names values
    (6, 'Matt');
    insert into names values
    (3, 'Wyatt');
    insert into names values
    (7, 'Jenny');
    insert into names values
    (4, 'Joel');


    create or replace view org_view (id, level_0, level_1)
    as
    select a.id,
    case when count(c.id) = 2
    then b.id
    else null end as level_0,
    case when count(c.id) = 3
    then b.id
    else null end as level_1
    from org_chart a,
    org_chart b,
    org_chart c
    where
    a.left between b.left and b.right
    and
    c.left between b.left and b.right
    and
    a.left between c.left and c.right
    group by a.id, b.id;

    col level_0 format a8
    col level_1 like level_0
    select a.id, b.name,
    decode(max(level_0),1,'Julie',2,'Andrew',3,'Wyatt') "level_0",
    decode(max(level_1),1,'Julie') "level_1"
    from org_view a, names b
    where a.id=b.id
    group by a.id, b.name
    order by 1;

    ID NAME level_0 level_1
    ---------- ---------- -------- --------
    1 Julie
    2 Andrew Julie
    3 Wyatt Julie
    4 Joel Julie
    5 Mark Andrew Julie
    6 Matt Andrew Julie
    7 Jenny Wyatt Julie

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    How about tackling the preformance problem with the CONNECT BY query?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •  


    Click Here to Expand Forum to Full Width