[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

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

Posted on 2004-10-27
2
Medium Priority
?
262 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
Comment
Question by:flashsubbu
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 22

Accepted Solution

by:
Helena Marková earned 375 total points
ID: 12430488
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
ID: 12433652
Thanks Henka.
Thanks for the Solution. It really worked.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

650 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