DB2 Hierarchy Table Question

dil77 · #1 (**permalink**) 05-13-09, 12:05

Anyone able to help with what I am sure is a common problem.

We have a table with two values
1) - Organisation Id
2) - Superior Organisation Id

I can easily find the superior org id of a given organisation id. The question is however how can I do this recursively (and efficiently)? - If we assume that the hierarchy can be a max of 10 deep do I have to do

Code:

select 
O1.ORG_UNIT_ID AS ORG_UNIT_ID_1, 
O2.ORG_UNIT_ID AS ORG_UNIT_ID_2, 
O3.ORG_UNIT_ID AS ORG_UNIT_ID_3,
O4.ORG_UNIT_ID AS ORG_UNIT_ID_4, 
O5.ORG_UNIT_ID AS ORG_UNIT_ID_5, 
O6.ORG_UNIT_ID AS ORG_UNIT_ID_6, 
O7.ORG_UNIT_ID AS ORG_UNIT_ID_7, 
O8.ORG_UNIT_ID AS ORG_UNIT_ID_8, 
O9.ORG_UNIT_ID AS ORG_UNIT_ID_9, 
O10.ORG_UNIT_ID AS ORG_UNIT_ID_10
from ORG_UNITS o1
LEFT OUTER JOIN ORG_UNITS O2
ON O1.SUPERIOR_ORG_UNIT_ID = O2.ORG_UNIT_ID
LEFT OUTER JOIN ORG_UNITS O3
ON O2.SUPERIOR_ORG_UNIT_ID = O3.ORG_UNIT_ID
LEFT OUTER JOIN ORG_UNITS O4
ON O3.SUPERIOR_ORG_UNIT_ID = O4.ORG_UNIT_ID
LEFT OUTER JOIN ORG_UNITS O5
ON O4.SUPERIOR_ORG_UNIT_ID = O5.ORG_UNIT_ID
LEFT OUTER JOIN ORG_UNITS O6
ON O5.SUPERIOR_ORG_UNIT_ID = O6.ORG_UNIT_ID
LEFT OUTER JOIN ORG_UNITS O7
ON O6.SUPERIOR_ORG_UNIT_ID = O7.ORG_UNIT_ID
LEFT OUTER JOIN ORG_UNITS O8
ON O7.SUPERIOR_ORG_UNIT_ID = O8.ORG_UNIT_ID
LEFT OUTER JOIN ORG_UNITS O9
ON O8.SUPERIOR_ORG_UNIT_ID = O9.ORG_UNIT_ID
LEFT OUTER JOIN ORG_UNITS O10
ON O9.SUPERIOR_ORG_UNIT_ID = O10.ORG_UNIT_ID

This doesn't seem terribly efficient to me - can anyone help ?

rdutton · #2 (**permalink**) 05-13-09, 12:21

Google: DB2 recursive sql

dil77 · #3 (**permalink**) 05-13-09, 12:37

ok many thanks had a quick look and got the following sql together which works very nicely.

Code:

with t1 (superior_org_unit_id,org_unit_id) as (select superior_org_unit_id, org_unit_id from ORG_UNITS where org_unit_id in 
(select superior_org_unit_id from ORG_UNITS) union all select t2.org_unit_id,t2.superior_org_unit_id
 from ORG_UNITS as t2, t1 where t2.org_unit_id = t1.org_unit_id) select org_unit_id from ORG_UNITS

is there a way of using this within a VIEW ? so that I can use the values in other queries ?

rdutton · #4 (**permalink**) 05-13-09, 12:40

Sure, it's just another sql statement. Just add the view declaration.

dil77 · #5 (**permalink**) 05-14-09, 08:07

Not sure this is what I need really.

Using the inner join method I am able to get the output of the hierarchy as one row. I can then directly refer to each element.

The sql posted above gives the hierachy, but each org id is a separate row. - No use when I want to add this info in with other elements from different tables.

I suspect that if I want to have this available in the view in the same row then I will need to use the inner join method.
Happy to be proved wrong if anyone has any ideas as to otherwise

many thanks

dil77 · #6 (**permalink**) 05-14-09, 09:45

Could anyone confirm for me

1) CTE (common table expressions) cannot be used in views ? - As I understand it CTE's are akin to temporary views which are only used for the lifespan of the query (unlike views whihc can be used any number of times)

2) CTE's must have the column names defined (there is no wildcard attribute)

As far as I can see the only way to get what I need - which is a view of the organisation hierachy is to use the join method I outline in the first post.

eg. assuming records
child -> parent
1->2
2->3
3->6
4->5
5->7

if I wanted org hierarchy of child 1 output would be something like
org1->org2->org3->org4->org5
1->2->3->6->null

any ideas greatly appreciated if I am incorrect on this

Stealth_DBA · #7 (**permalink**) 05-16-09, 00:54

dil77,

1) CTE can be used in Views (in the LUW 9.5 manual. I don't have access to z/OS manual at the moment, but I believe Views can have CTE also).

2) CTE do not require having column names defined. As long as you use columns backed by an actual table, DB2 will used the columns attributes. But if you use constants or derived values, you need to use columns and define the datatypes. NOTE: I always define the columns anyway as it is a good documentation and easy to fined what the columns in the CTE are instead of looking at the SQL and/or going to the Catalog tables to find the info.

As for getting the results you want, What do you want.

1 row starting at a specified node?
ex:
for Node 1: 1, 2, 3, 6, null, null, null, null, null, null
or
for Node 2: 2, 3, 6, null, null, null, null, null, null
etc.

Multiple rows (1 row for every distinct node)?

1, 2, 3, 6,, NULL, NULL, NULL, NULL, NULL, NULL
2, 3, 6,, NULL, NULL, NULL, NULL, NULL, NULL, NULL
3, 6,, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
6, , NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
4, 5, 7,, NULL, NULL, NULL, NULL, NULL, NULL, NULL
5, 7, , NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
7, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL

Internet Services

Web Development

Digital Marketing

Consumer Tech