Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2003
    Posts
    218

    Unanswered: Hierarchical query

    DB2 env: v8.1.4a udb ese on win2k platform.

    I have a table

    that has 3 columns

    folderid parentid scope

    ------------------------------
    1 0 1
    2 1 0
    3 2 0


    The input to this query will be a folder id and the output will be a
    folder id and parent id whose scope is 1.
    In short I want to find the folder id ( parent folderid ) for a given
    folder id whoose scope is 1

    So in effect if I pass in folder id 3 , it will find that it has a
    scope of 0 , hence it will traverse to its immediate parent which is 2
    whose folder id also has a value of scope 0 and hence it moves to its
    parent folder id whose scope is 1 and the output should return be
    folder id of 1 and parent id is 0.

    Any help is greatly appreciated. Thanks in advance.

  2. #2
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    This would do it. You could probably stop the recursion once 'resolved_hierarchy' contains a 'scope' value of 1 but I suppose that depends on what your actual data is like.

    Code:
    with resolved_hierarchy (folderid, parentid, scope) as
    (
     select folderid, parentid, scope
     from yourTable
     where folderid = 3
     union all
     select b.folderid, b.parentid, b.scope
     from resolved_hierarchy a
     ,    yourTable b
     where a.parentid = b.folderid
    )
    select * from resolved_hierarchy
    where scope = 1

  3. #3
    Join Date
    Sep 2003
    Posts
    218
    Hi Damian,

    Thanks a ton for your timely help. I really appreciate it.

    I tried this one out and here's the output...

    FOLDERID PARENTID SCOPE
    ----------- ----------- -----------
    SQL0347W The recursive common table expression "RESOLVED_HIERARCHY"
    may contain an infinite loop. SQLSTATE=01605

    1 0 1

    1 record(s) selected with 1 warning messages printed.

    Any ideas?

    Many thanks once again.

  4. #4
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    And that's the answer you required! Folderid = 1, ParentId = 0 and scope = 0.

    You can ignore the warning if you can be sure that you'd never enter an infinite loop with the recursion. This would only happen where a folderid would reference a parentid that happens to be referenced as folderid previously in the hierarchical chain.

    You can write recursive sql that DB2 is clever enough to know cannot enter an infinite loop.

    The example below increments the 'lvl' value by 1 on every iteration. Once it hits 5, i.e. the 5th level, the recursion will stop. Obviously, this is only any use when you know how many levels your longest chain might have.
    Code:
    with resolved_hierarchy (folderid, parentid, scope, lvl) as
    (
     select folderid, parentid, scope, 0
     from yourTable
     where folderid = 3
     union all
     select b.folderid, b.parentid, b.scope, lvl + 1
     from resolved_hierarchy a
     ,    yourTable b
     where a.parentid = b.folderid
     and a.lvl < 5
    )
    select * from resolved_hierarchy
    where scope = 1
    ;
    You can also write code that DB2 is NOT clever enough to know will never enter an infinte loop...
    Code:
    with resolved_hierarchy (folderid, parentid, scope, hierarchy_chain) as
    (
     select folderid, parentid, scope, cast('+'||cast(folderid as char(1))||'+'||cast(parentid as char(1)) as varchar(100))||'+'
     from yourTable
     where folderid = 3
     union all
     select b.folderid, b.parentid, b.scope, hierarchy_chain||cast(b.parentid as char(1))||'+'
     from resolved_hierarchy a
     ,    yourTable b
     where a.parentid = b.folderid
     and locate('+'||cast(b.parentid as char(1))||'+',hierarchy_chain) = 0
    )
    select * from resolved_hierarchy where scope = 1
    ;
    The example above maintains a 'mapping' of the hierarchy chain and will only recurse beyond a particular level if it finds that the mapping does not already hold a reference to a particular parent. This has the effect of 'breaking' an infinite loop if one is encountered.

    HTH
    Last edited by Damian Ibbotson; 09-01-04 at 10:13.

  5. #5
    Join Date
    Sep 2003
    Posts
    218
    thank you very much.

    Best Regards.

Posting Permissions

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