This discussion is archived
10 Replies Latest reply: Dec 31, 2012 7:11 PM by 982284 RSS

Oracle hierarchical Query Help Need

982284 Newbie
Currently Being Moderated
Hi ,
I'm in a deep trouble.

I want to make a hierarchical query in my hierarchical table in oracle. But I want the query will fetch the path information between two nodes.

Consider the below table.

------------------------------------------------------
Node Id | Node Name | Parent Id
-----------------------------------------------------
1 A 0
2 B 1
3 C 0
4 D 2
-----------------------------------------------------

What I want is I will provide 2 node id in the query. One is the source node and the other is the destination node.
so if I put Node 1 and Node 4 , I will want the output be

1->2->4

I just want one row return . Or it can be multiple row but will only holds the node of that path.

1
2
4
----

That means I don't want node 3 in the search result.


More over if I provide the source node as 4 and destination node as 1 then I will also get an output from the query like below -
4->2->1

That means the query search for the result in bi directional way.


If anybody can give me some information regarding that it will be great for me.

Edited by: user13276471 on Dec 31, 2012 3:21 AM
  • 1. Re: Oracle hierarchical Query Help Need
    Solomon Yakobson Guru
    Currently Being Moderated
    with t as (
               select 1 id,'A' name,0 parent_id from dual union all
               select 2,'B',1 from dual union all
               select 3,'C',0 from dual union all
               select 4,'D',2 from dual
              )
    select  ltrim(sys_connect_by_path(id,'-->'),'->') path
      from  t
      where connect_by_isleaf = 1
        and level > 1
      start with parent_id = 0
      connect by parent_id = prior id
    /
    
    PATH
    ------------
    1-->2-->4
    
    SQL> 
    SY.
  • 2. Re: Oracle hierarchical Query Help Need
    Solomon Yakobson Guru
    Currently Being Moderated
    Oops, you wanted leaf to root:
    with t as (
               select 1 id,'A' name,0 parent_id from dual union all
               select 2,'B',1 from dual union all
               select 3,'C',0 from dual union all
               select 4,'D',2 from dual
              )
    select  ltrim(sys_connect_by_path(id,'-->'),'->') path
      from  t
      where connect_by_isleaf = 1
        and level > 1
      start with id not in (select parent_id from t)
      connect by id = prior parent_id
    /
    
    PATH
    ----------
    4-->2-->1
    
    SQL> 
    SY.
  • 3. Re: Oracle hierarchical Query Help Need
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    Welcome to the forum!

    Assuming that you have 2 distinct nodes, :node_a and :node_b, you can find the hierarchy from one to the other like this:
    SELECT     SYS_CONNECT_BY_PATH (node_id, ',')     AS lineage
    FROM     table_x
    WHERE     node_id     IN (:node_a, :node_b)
    AND     LEVEL     > 1
    START WITH     node_id          IN (:node_a, :node_b)
    CONNECT BY     parent_id     = PRIOR node_id
    ;
    This will work if :node_a is an ancestor of :node_b, or if :node_b is an ancestor of :node_a.
    If neither is the ancestor of the other, then it will produce 0 rows.
  • 4. Re: Oracle hierarchical Query Help Need
    padders Pro
    Currently Being Moderated
    I could be mistaken but I thought delimiter in SYS_CONNECT_BY_PATH was mandatory.
  • 5. Re: Oracle hierarchical Query Help Need
    Solomon Yakobson Guru
    Currently Being Moderated
    I will assume that id increases from parent to child. Then if you want a single query where you just provide start and end id:
    SQL> variable start_id number
    SQL> variable end_id number
    SQL> exec :start_id := 1;
    
    PL/SQL procedure successfully completed.
    
    SQL> exec :end_id := 4;
    
    PL/SQL procedure successfully completed.
    
    SQL> with t as (
      2             select 1 id,'A' name,0 parent_id from dual union all
      3             select 2,'B',1 from dual union all
      4             select 3,'C',0 from dual union all
      5             select 4,'D',2 from dual
      6            )
      7  select  ltrim(sys_connect_by_path(id,'-->'),'->') path
      8    from  t
      9    where connect_by_isleaf = 1
     10      and level > 1
     11    start with id = :start_id
     12    connect by    (
     13                   :start_id < :end_id and parent_id = prior id
     14                  )
     15               or (
     16                   :start_id >= :end_id and id = prior parent_id
     17                  )
     18  /
    
    PATH
    --------------------
    1-->2-->4
    
    SQL> exec :start_id := 4;
    
    PL/SQL procedure successfully completed.
    
    SQL> exec :end_id := 1;
    
    PL/SQL procedure successfully completed.
    
    SQL> with t as (
      2             select 1 id,'A' name,0 parent_id from dual union all
      3             select 2,'B',1 from dual union all
      4             select 3,'C',0 from dual union all
      5             select 4,'D',2 from dual
      6            )
      7  select  ltrim(sys_connect_by_path(id,'-->'),'->') path
      8    from  t
      9    where connect_by_isleaf = 1
     10      and level > 1
     11    start with id = :start_id
     12    connect by    (
     13                   :start_id < :end_id and parent_id = prior id
     14                  )
     15               or (
     16                   :start_id >= :end_id and id = prior parent_id
     17                  )
     18  /
    
    PATH
    --------------------
    4-->2-->1
    
    SQL> 
    SY.
  • 6. Re: Oracle hierarchical Query Help Need
    982284 Newbie
    Currently Being Moderated
    Thanks a lot. I think this is the right answer I was looking for.
    You were so quick.

    - Thanks again for your quicker response.
  • 7. Re: Oracle hierarchical Query Help Need
    982284 Newbie
    Currently Being Moderated
    I need another information.
    Consider the following case ---


    NodeId --- ParentId
    =============
    1 >>>>>> 0
    2 >>>>>> 1
    3 >>>>>> 2
    4 >>>>>> 3
    5 >>>>>> 0
    6 >>>>>> 5


    Here I want to query in the database table that whether there is a path between node 3 and node 5 ?
    The previous query you provided work up to root node.

    Here my expected result is 3-->2-->1-->0-->5

    Again if I query in the table to get the path between 1 and 3 , I want to get the output in the following way -
    1-->2-->3

    Can you please guide me how I can get it ?

    Thanks.

    Edited by: user13276471 on Dec 31, 2012 9:14 AM
  • 8. Re: Oracle hierarchical Query Help Need
    982284 Newbie
    Currently Being Moderated
    Hi,

    The note you have provided is right -
    "
    This will work if :node_a is an ancestor of :node_b, or if :node_b is an ancestor of :node_a.
    If neither is the ancestor of the other, then it will produce 0 rows.
    "

    But in my case there can be two nodes when both of them are under one internal node or root node.
    Suppose Node B and Node C are child of Node A.

    Then If i search for a path between node B and node C then I don't get the path B-->C-->A

    Please let me know how can I get that ?
  • 9. Re: Oracle hierarchical Query Help Need
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    Tricky problem!

    So, if the source (:src_nodeid) is an ancestor of the destination (:dst_nodeid), then you want just the top-down path from :src_nodeid to :dst_nodeid. But if :src_nodeid is not an ancestor of :dest_nodeid, then you need to do a Yo-Yo Query, where you first trace the path up from :src_nodeid to 0 (which is the ancestor of all), and then down to :dst_nodeid.

    Here's one way to do that:
    VARIABLE  src_nodeid  NUMBER
    VARIABLE  dst_nodeid  NUMBER
    EXEC     :src_nodeid  := 3;
    EXEC     :dst_nodeid  := 5;
    
    WITH     top_down_results     AS
    (
         SELECT     CONNECT_BY_ROOT  nodeid               AS start_nodeid
         ,     CONNECT_BY_ROOT  parentid          AS start_parentid
         ,     SYS_CONNECT_BY_PATH (nodeid, '-->')     AS top_down_path
         FROM     table_x
         WHERE     nodeid     = :dst_nodeid
         START WITH     nodeid          = :src_nodeid
               OR     parentid     = 0
         CONNECT BY     parentid     = PRIOR nodeid
              AND     PRIOR nodeid     != :dst_nodeid
    )
    ,     best_top_down     AS
    (
         SELECT       MIN (start_nodeid)  KEEP (DENSE_RANK LAST ORDER BY start_parentid)
                             AS start_nodeid
         ,       MAX (start_parentid)     AS start_parentid
         ,       MIN (top_down_path) KEEP (DENSE_RANK LAST ORDER BY start_parentid)
                                     AS top_down_path
         FROM       top_down_results
    )
    SELECT     CASE
             WHEN  start_nodeid     = :src_nodeid
             THEN  SUBSTR (top_down_path, 4)     -- 4 = 1 + LENGTH ('-->')
             ELSE  :src_nodeid || (
                                         SELECT  SYS_CONNECT_BY_PATH (parentid, '-->')
                             FROM    table_x
                             WHERE   CONNECT_BY_ISLEAF     = 1
                             START WITH  nodeid  = :src_nodeid     
                             CONNECT BY      nodeid      = PRIOR parentid
                                     ) 
                         || top_down_path
         END     AS path
    , start_nodeid
    FROM     best_top_down
    ;
    Putting the bottom-up CONNECT BY query inside the CASE expression means that we won't waste time doing it when it's not necessary.

     

    Whenever you have a question, please post sample data in a form that people can use to re-create the problem and test their ideas. You can either use CREATE TABLE and INSERT statements, such as:
    CREATE TABLE     table_x
    (   nodeid     NUMBER (6)     PRIMARY KEY
    ,   parentid     NUMBER (6)     NOT NULL
    );
    
    INSERT INTO table_x (nodeid, parentid) VALUES (1, 0);
    INSERT INTO table_x (nodeid, parentid) VALUES (2, 1);
    INSERT INTO table_x (nodeid, parentid) VALUES (3, 2);
    INSERT INTO table_x (nodeid, parentid) VALUES (4, 3);
    INSERT INTO table_x (nodeid, parentid) VALUES (5, 0);
    INSERT INTO table_x (nodeid, parentid) VALUES (6, 5);
    COMMIT;
    or you can post a WITH clause, like Solomon did earlier.
  • 10. Re: Oracle hierarchical Query Help Need
    982284 Newbie
    Currently Being Moderated
    Thanks for your response.
    It helped me a lot.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points