CODEWITHSUNDEEP
sqloracle-databasehierarchical-data
omar
omar

Reputation: 446

How to query parent organization in certain level?

We have 5 levels of organizations from bottom to top will be (Unit -> Section -> Department(Dep) -> General Department (GDep) -> Sector)

What I need is to get the GDep of all organizations lower than GDep.

So if we have this table

Child     Parent
--------  --------
Unit1     Section1
Unit2     Section1
Unit3     Dep1
Unit4     GDep1
Section1  GDep1
Dep1      GDep2
GDep1     Sector1
GDep2     Sector1

I need the query result to be

Child     Parent
--------  --------
Unit1     GDep1
Unit2     GDep1
Unit3     GDep2
Unit4     GDep1
Section1  GDep1
Dep1      GDep2

This is the original query

    select DISTINCT HAOU_PARENTS.ORGANIZATION_ID, HAOU_PARENTS.NAME, HAOU_PARENTS.TYPE, HAOU_CHILDS.ORGANIZATION_ID, HAOU_CHILDS.NAME,  HAOU_CHILDS.TYPE, SYS_CONNECT_BY_PATH(HAOU_CHILDS.NAME, '#'), LEVEL
FROM per_org_structure_elements POSE
    JOIN hr_all_organization_units HAOU_PARENTS ON (POSE.ORGANIZATION_ID_PARENT = HAOU_PARENTS.ORGANIZATION_ID)
    JOIN hr_all_organization_units HAOU_CHILDS ON (POSE.ORGANIZATION_ID_CHILD = HAOU_CHILDS.ORGANIZATION_ID)
WHERE 1=1
    AND pose.org_structure_version_id = 2061
START WITH UPPER(HAOU_PARENTS.TYPE) = 'GDEP'
    CONNECT BY PRIOR POSE.ORGANIZATION_ID_CHILD = POSE.ORGANIZATION_ID_PARENT
ORDER BY 4
;

The results was far away from the desired output, and pretty much random.

"per_org_structure_elements" contains the org hierarchy , "hr_all_organization_units" contains org details like name, and type.

Upvotes: 0

Views: 2046

Answers (1)

Alex Poole
Alex Poole

Reputation: 191415

Assuming your table has an org_type column that uses the values you described for the various levels of organisation, you can do:

select child, connect_by_root(child) as gdep
from your_table
where org_type in ('Unit', 'Section', 'Dep')
connect by parent = prior child
start with org_type = 'GDep';

The connect_by_root() clause gives you the child value from the root of the hierarchy, which will be the GDep of the start with rows.

As the hierarchy isn't defined anywhere (that we know of) the lower level that you want to include can be listed explicitly, though if it's well-defined that shouldn't be needed, as the children should be 'lower' anyway.

Demo with your example data in a CTE, with added org_type values:

with your_table(child, parent, org_type) as (
            select 'Unit1', 'Section1', 'Unit' from dual
  union all select 'Unit2', 'Section1', 'Unit' from dual
  union all select 'Unit3', 'Dep1', 'Unit' from dual
  union all select 'Unit4', 'GDep1', 'Unit' from dual
  union all select 'Section1', 'GDep1', 'Section' from dual
  union all select 'Dep1', 'GDep2', 'Dep' from dual
  union all select 'GDep1', 'Sector1', 'GDep' from dual
  union all select 'GDep2', 'Sector1', 'GDep' from dual
)
select child, connect_by_root(child) as gdep
from your_table
where org_type in ('Unit', 'Section', 'Dep')
connect by parent = prior child
start with org_type = 'GDep';

CHILD    GDEP    
-------- --------
Section1 GDep1   
Unit1    GDep1   
Unit2    GDep1   
Unit4    GDep1   
Dep1     GDep2   
Unit3    GDep2

Based on the query you added, the equivalent would be (I think, without seeing the data):

connect_by_root(haou_parents.name) as gdep

With more made-up data based on your sample:

with per_org_structure_elements(org_structure_version_id, organization_id_child,
    organization_id_parent) as (
            select 2061, 1, 5 from dual
  union all select 2061, 2, 5 from dual
  union all select 2061, 3, 6 from dual
  union all select 2061, 4, 7 from dual
  union all select 2061, 5, 7 from dual
  union all select 2061, 6, 8 from dual
  union all select 2061, 7, 9 from dual
  union all select 2061, 8, 9 from dual
),
hr_all_organization_units(organization_id, name, type) as (
            select 1, 'Unit1', 'Unit' from dual
  union all select 2, 'Unit2', 'Unit' from dual
  union all select 3, 'Unit3', 'Unit' from dual
  union all select 4, 'Unit4', 'Unit' from dual
  union all select 5, 'Section1', 'Section' from dual
  union all select 6, 'Dep1', 'Dep' from dual
  union all select 7, 'GDep1', 'GDep' from dual
  union all select 8, 'GDep2', 'GDep' from dual
  union all select 9, 'Sector1', 'Sector' from dual
)
select distinct haou_parents.organization_id,
  haou_parents.name,
  haou_parents.type,
  haou_childs.organization_id,
  haou_childs.name,
  haou_childs.type,
  sys_connect_by_path(haou_childs.name, '#') as path,
  connect_by_root(haou_parents.name) as gdep,
  level
from per_org_structure_elements pose
join hr_all_organization_units haou_parents
on (pose.organization_id_parent = haou_parents.organization_id)
join hr_all_organization_units haou_childs
on (pose.organization_id_child = haou_childs.organization_id)
where 1=1
and pose.org_structure_version_id = 2061
start with upper(haou_parents.type) = 'GDEP'
connect by prior pose.organization_id_child = pose.organization_id_parent
order by 4;

gets

ORGANIZATION_ID NAME     TYPE    ORGANIZATION_ID NAME     TYPE    PATH                 GDEP          LEVEL
--------------- -------- ------- --------------- -------- ------- -------------------- -------- ----------
              5 Section1 Section               1 Unit1    Unit    #Section1#Unit1      GDep1             2
              5 Section1 Section               2 Unit2    Unit    #Section1#Unit2      GDep1             2
              6 Dep1     Dep                   3 Unit3    Unit    #Dep1#Unit3          GDep2             2
              7 GDep1    GDep                  4 Unit4    Unit    #Unit4               GDep1             1
              7 GDep1    GDep                  5 Section1 Section #Section1            GDep1             1
              8 GDep2    GDep                  6 Dep1     Dep     #Dep1                GDep2             1

Upvotes: 1

Related Questions