Thread: DB2 Hierarchy Table Question

1. Registered User
Join Date
May 2009
Posts
4

Unanswered: DB2 Hierarchy Table Question

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 ?

2. Registered User
Join Date
Dec 2008
Posts
76
Google: DB2 recursive sql

3. Registered User
Join Date
May 2009
Posts
4
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 ?

4. Registered User
Join Date
Dec 2008
Posts
76
Sure, it's just another sql statement. Just add the view declaration.

5. Registered User
Join Date
May 2009
Posts
4
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

6. Registered User
Join Date
May 2009
Posts
4
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

7. Registered User
Join Date
May 2009
Posts
509
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

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•