Link to home
Start Free TrialLog in
Avatar of flashsubbu
flashsubbu

asked on

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
ASKER CERTIFIED SOLUTION
Avatar of Helena Marková
Helena Marková
Flag of Slovakia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of flashsubbu
flashsubbu

ASKER

Thanks Henka.
Thanks for the Solution. It really worked.