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.
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;
where clause is applied after the hierarchy is retrieved.