Solved

Recursive Query : - Level skip possibility during recursive query searches for parent-child fields in a table

Posted on 2004-10-27
258 Views
Last Modified: 2006-11-17
Hi,

I have requirement like this.
I have a db table with many columns and columns of my interest are 2 (Parent_id field and child_id field).
Given a parent_Id, I need find all the child Ids and then recursively find all the children of these child_Ids and so on.
I have succeded in getting this using "Start With" + "Connect By" and "PRIOR" clause in Oracle (9204 version of Oracle).

Select Distinct Child_IDS from the table start with parent_id = <input Parent_id>
Connect by parent_id = PRIOR child_id

This query runs absolutely fine.

My next requirement is I need to do this searching based on the LEVEL.
Suppose a LEVEL is given as input , then I need to do the searching  starting from that Level and all the child id's from that level ( then its children etc recursively) and ignore all the Ids before this given Level. For example, if the input recieved for LEVEL is 3 (also ZERO for skipping), the results should skip both level-1 and level-2 IDs as well as any children at level-3,and give all the children starting from level 4.

How can I get the Level ID with "Connect by" clause in the SQL query and how can I use it skip the level.

Help from all you good people required.

Thanks
Subbi
0
Question by:flashsubbu
    2 Comments
     
    LVL 22

    Accepted Solution

    by:
    You can try this:

    Select Child_IDS from table WHERE level>= <input level> start with parent_id = <input Parent_id>
    Connect by parent_id = PRIOR child_id

    Here is an example:
    SQL> select level,o.os_objekt_id,o.os_objekt_id_nad from os_struktura o where level>=1
      2  start with o.os_objekt_id=10004 connect by o.os_objekt_id_nad=PRIOR o.os_objekt_id;

         LEVEL OS_OBJEKT_ID OS_OBJEKT_ID_NAD
    ---------- ------------ ----------------
             1        10004
             2        10005 10004
             3        10006 10005
             4        10009 10006
             4        10010 10006
             4        10011 10006
             4        10012 10006
             4        10013 10006
             4        10014 10006
             4        10017 10006
             4        10016 10006
             3        10007 10005
             4        10008 10007
             4        10018 10007

    14 rows selected

    SQL> select level,o.os_objekt_id,o.os_objekt_id_nad from os_struktura o where level>=2
      2  start with o.os_objekt_id=10004 connect by o.os_objekt_id_nad=PRIOR o.os_objekt_id;

         LEVEL OS_OBJEKT_ID OS_OBJEKT_ID_NAD
    ---------- ------------ ----------------
             2        10005 10004
             3        10006 10005
             4        10009 10006
             4        10010 10006
             4        10011 10006
             4        10012 10006
             4        10013 10006
             4        10014 10006
             4        10017 10006
             4        10016 10006
             3        10007 10005
             4        10008 10007
             4        10018 10007

    13 rows selected

    SQL> select o.os_objekt_id,o.os_objekt_id_nad from os_struktura o where level>=2
      2  start with o.os_objekt_id=10004 connect by o.os_objekt_id_nad=PRIOR o.os_objekt_id;

    OS_OBJEKT_ID OS_OBJEKT_ID_NAD
    ------------ ----------------
           10005 10004
           10006 10005
           10009 10006
           10010 10006
           10011 10006
           10012 10006
           10013 10006
           10014 10006
           10017 10006
           10016 10006
           10007 10005
           10008 10007
           10018 10007

    13 rows selected
    0
     

    Author Comment

    by:flashsubbu
    Thanks Henka.
    Thanks for the Solution. It really worked.
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Suggested Solutions

    I guess that all of us know that caching the data usually increase the performance, but I worried if all of us are aware about the risk that caching the data provides and how to minimize this.  That’s the reason why I decided to write this short art…
    Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    933 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now