CODEWITHSUNDEEP
sqloracle-databaseloopshierarchical-data
Marc
Marc

Reputation: 352

Oracle - Loop through hierarchised records

So, since I struggled to find an accurate title, I think a detailled shema will be much more understandable.

I have this table PROGRAM that I will reduce to 3 fields for simplicity:

ID |NAME    |ID_ORIGINAL_PROGRAM
1  |name_1  |
2  |name_2  |1
3  |name_3  |1
4  |name_4  |2
5  |name_5  |3      
6  |name_6  |
7  |name_7  |6

I'm trying to find a query that will allow me, with any ID as parameter to gather all the related programs to this id. And I need to be able to send a parameter than does not necessarily has to be the "father" id of the hierarchy.

For example, if parameter ID is 1, then results will be:

ID
2
3
4
5

If parameter ID is 4, then the results will be:

ID
1
2
3
5

It seems like I'm missing some kind "loop" logic that I can't clearly identify. I looked up at "CONNECT BY PRIOR" but was not able to grasp the concept enough to understand how to deploy it.

Edit: So it seems I found a way through:

    SELECT ID
      FROM PROGRAM
START WITH ID = 67256
CONNECT BY NOCYCLE ID_ORIGINAL_PROGRAM = PRIOR ID
                   OR ID = PRIOR ID_ORIGINAL_PROGRAM
order by ID

I'm a bit concerned by the performances though (it takes 1 second to perform)

Upvotes: 0

Views: 106

Answers (1)

Barbaros Özhan
Barbaros Özhan

Reputation: 65218

I suppose you need 

with program( id, id_original_program ) as
(
 select 1, null from dual union all
 select 2, 1    from dual union all
 select 3, 1    from dual union all
 select 4, 2    from dual union all
 select 5, 3    from dual union all
 select 6, null from dual union all
 select 7, 6    from dual 
)
 select id, sys_connect_by_path(id, '->') as path
   from program
  where id_original_program is not null 
 connect by prior id =  id_original_program
 start with id = 1 -- 4
 order by id;

ID  PATH
2   ->1->2
3   ->1->3
4   ->1->2->4
5   ->1->3->5

if value 4 is substituted, then you get 

ID  PATH
4   ->4

only.

Whether you substitute 1 or 4, you'll get the same result for your query.

Upvotes: 1

Related Questions