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
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 ?
ok many thanks had a quick look and got the following sql together which works very nicely.
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 ?
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.
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?
for Node 1: 1, 2, 3, 6, null, null, null, null, null, null
for Node 2: 2, 3, 6, null, null, null, null, null, null