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

Oracle hierarchical Query Help Need - Path between two siblings node

982284 Newbie
Currently Being Moderated
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 Oracle ACE Director
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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
    982284 Newbie
    Currently Being Moderated
    I'm really impressed.
    This is the exact answer I was looking for.


    Thanks for your kind and quick help.

Legend

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