Results 1 to 7 of 7
  1. #1
    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. #2
    Join Date
    Dec 2008
    Posts
    76
    Google: DB2 recursive sql

  3. #3
    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. #4
    Join Date
    Dec 2008
    Posts
    76
    Sure, it's just another sql statement. Just add the view declaration.

  5. #5
    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. #6
    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. #7
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    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
  •