Recursive Query : - Level skip possibility during recursive query searches for parent-child fields in a table
Posted on 2004-10-27
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.