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

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
flashsubbuAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Helena Markováprogrammer-analystCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
flashsubbuAuthor Commented:
Thanks Henka.
Thanks for the Solution. It really worked.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.