Super Contributor.. JHarris941 Super Contributor..
Super Contributor..
498 views

SQL for Org Unit Hierarchy

Hello All,

I'm trying to create SQL that will return a single column of every level org units. The below query does the job, but I need to return the results in a single column.  That way I can eliminate level 3 org units.  Anyone have a clean way of doing this? I know I could do a UNION but that would be a massive validation query.

select
kou5.org_unit_name org_unit1,
kou4.org_unit_name org_unit2,
kou3.org_unit_name org_unit3,
kou2.org_unit_name org_unit4,
kou1.org_unit_name ORG_UNIT5
from
krsc_org_units kou1,
krsc_org_units kou2,
krsc_org_units kou3,
krsc_org_units kou4,
krsc_org_units kou5

where 1=1
and kou2.org_unit_id(+) = kou1.parent_org_unit_id
and kou3.org_unit_id(+) = kou2.parent_org_unit_id
and kou4.org_unit_id(+) = kou3.parent_org_unit_id
and kou5.org_unit_id(+) = kou4.parent_org_unit_id
--only enabled org units--
and
(kou1.enabled_flag = 'Y'
or kou2.enabled_flag = 'Y'
or kou3.enabled_flag = 'Y'
or kou4.enabled_flag = 'Y'
or kou5.enabled_flag = 'Y'

 

Thanks,

Jajcen

0 Likes
3 Replies
Outstanding Contributor.. Amishra Outstanding Contributor..
Outstanding Contributor..

Re: SQL for Org Unit Hierarchy

Try XMLAGG. 

 

Regards,
Ajay Mishra
0 Likes
jphahn Trusted Contributor.
Trusted Contributor.

Re: SQL for Org Unit Hierarchy

Hello Jajcen

Try the following code:

SELECT    lpad(ORG_UNIT_NAME, LENGTH(ORG_UNIT_NAME) + LEVEL * 2 - 2) "OrgUnit"
FROM      KRSC_ORG_UNITS_NLS
WHERE     1=1
AND       CATEGORY_CODE IN('DEPARTMENT', 'GROUP', 'TEAM')
START WITH upper(ORG_UNIT_NAME) IN upper('I')
CONNECT BY PRIOR ORG_UNIT_ID = PARENT_ORG_UNIT_ID
ORDER SIBLINGS BY ORG_UNIT_NAME;

You may pla with the numbers in the lpad function to modify the identation level. The root of the org units is in the 5th line; in my example upper('I') where I is the top-level of the informatics department.

Hope this helps

Best regards

Jean-Paul

0 Likes
jphahn Trusted Contributor.
Trusted Contributor.

Re: SQL for Org Unit Hierarchy

You can also filter for active Org Units only by adding:

AND       ENABLED_FLAG = 'Y'

Best regards

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.