3 Replies Latest reply: Dec 31, 2012 9:08 PM by user13276471 RSS

    Oracle hierarchical Query Help Need - Path between two siblings node

    user13276471
      I want to find the path between two nodes from oracle Hierarchical Table.

      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

      So the query need to work in both the cases. Where root nod can act as an intermediate node or not.

      Can you please guide me how I can get it ?

      Thanks.
        • 1. Re: Oracle hierarchical Query Help Need - Path between two siblings node
          damorgan
          I have no idea what you are asking or in what version.

          What, for example, is a hierarchical table? Provide the DDL. Perhaps you mean to infer a table with a recursive relationship but that is far from clear.

          Assuming that is correct the syntax element you want is CONNECT BY but what you mean by "the path between" is equally mysterious.

          Please provide answers to all the questions asked and clarity to what your terms mean. Perhaps then we can help you.
          • 2. Re: Oracle hierarchical Query Help Need - Path between two siblings node
            Frank Kulash
            Hi,
            user13276471 wrote:
            I want to find the path between two nodes from oracle Hierarchical Table.

            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
            What query is that? If you're referering to another thread, then post a link, such as {message:id=10769125}
            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

            So the query need to work in both the cases. Where root nod can act as an intermediate node or not.

            Can you please guide me how I can get it ?
            I think you want something like this:
            WITH     bottom_up_from_src    AS
            (
                 SELECT     nodeid
                 ,     parentid
                 FROM     table_x
                 START WITH     nodeid      = :src_nodeid
                 CONNECT BY     nodeid   = PRIOR parentid
            )
            ,     bottom_up_from_dst     AS
            (
                 SELECT     *
                 FROM     bottom_up_from_src
                UNION ALL
                 SELECT     parentid     AS nodeid
                 ,     nodeid          AS parentid
                 FROM     table_x
                 WHERE     nodeid     NOT IN (
                                                  SELECT  nodeid
                                           FROM    bottom_up_from_src
                                              )
                 START WITH     nodeid        = :dst_nodeid
                 CONNECT BY     nodeid        = PRIOR parentid
            )
            SELECT      :src_nodeid || SYS_CONNECT_BY_PATH (parentid, '-->')     AS display_path
            FROM       bottom_up_from_dst
            WHERE       parentid     = :dst_nodeid
            START WITH  nodeid     = :src_nodeid
            CONNECT BY  nodeid     = PRIOR parentid
            ;
            This will show how you can get from :src_nodeid to dst_nodeid, moving up or down the hierarchy one step at a time. It will work regardless of whether
            <ul>
            <li>:src_nodeid is an ancestor of :dst_nodeid, or
            <li>:src_nodeid is a descendant of :dst_nodeid, or
            <li>both :src_nodeid and :dst_nodeid are descendants of some other node (such as 0).
            </ul>


             

            I hope this answers your question.
            If not, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only), and also post the results you want from that data.
            Explain, using specific examples, how you get those results from that data.
            Always say which version of Oracle you're using (e.g., 11.2.0.2.0). This is always important, but especially so with CONNECT BY queries, because every version since Oracle 7 has had significant improvements in this area.
            See the forum FAQ {message:id=9360002}
            • 3. Re: Oracle hierarchical Query Help Need - Path between two siblings node
              user13276471
              I'm really impressed.
              This is the exact answer I was looking for.


              Thanks for your kind and quick help.