10 Replies Latest reply: Dec 31, 2012 9:11 PM by user13276471 RSS

    Oracle hierarchical Query Help Need

    user13276471
      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
          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
            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
              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
                I could be mistaken but I thought delimiter in SYS_CONNECT_BY_PATH was mandatory.
                • 5. Re: Oracle hierarchical Query Help Need
                  Solomon Yakobson
                  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
                    user13276471
                    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
                      user13276471
                      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
                        user13276471
                        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
                          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
                            user13276471
                            Thanks for your response.
                            It helped me a lot.