Oracle Hierarchy - How to get a particular level record in a query?-CodePudding

We have the following structure for Organizations.

create table orgs (
    org_id number
  , org_name varchar2(250)
  , org_type varchar2(10)
  , parent_org_id number 
)
/

insert into orgs values ( 1, 'President', 'PRES', null );
insert into orgs values ( 2, 'Information Technology Department', 'DEP', 1 );
insert into orgs values ( 3, 'Software Development Division', 'DIV', 2 );
insert into orgs values ( 4, 'Database Unit', 'UNIT', 3 );
insert into orgs values ( 5, 'Developer Unit', 'UNIT', 3 );
insert into orgs values ( 6, 'Infrastracture Department', 'DEP', 1 );
insert into orgs values ( 7, 'Security Division', 'DIV', 6 );
insert into orgs values ( 8, 'System Admintrator Division', 'UNIT', 7 );


  select level, org_id, org_name, org_type
    from orgs
 connect
      by 
   prior org_id = parent_org_id
   start
    with parent_org_id is null

Query returns the result

What I'm trying to do is let's say I need to get the Department (Information Technology Department) of org_id 4 (Database Unit), how should I build the query for that?

Currently we built a function that returns the department id which basically loops until it reaches the DEP parent. But it has a performance issue.

CodePudding user response：

You can invert the hierarchy query to start from the Database Unit (org_id = 4) and filter to the department ancestor (org_type = 'DEP'):

select org_id, org_name
from orgs
where org_type = 'DEP'
connect by prior parent_org_id = org_id
start with org_id = 4;

The where clause is applied after the hierarchy is retrieved.

CodePudding user response：

You can try something like this (replace the '5' with what you want as starting org_id):

with lp (lev, org_id, org_name, org_type, parent_org_id, l_orgs) as (
select 1 as lev, org_id, org_name, org_type, parent_org_id , to_char(org_id) l_orgs
    from orgs
    where org_id=5
union all
select lev 1, o.org_id, o.org_name, l.org_type||'/'||o.org_type, o.parent_org_id, l_orgs||'/'||o.org_id
from orgs o join  lp l on (o.org_id=l.parent_org_id and l.org_type not like '