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
dBforums
>
Hierarchical query
09-01-04, 04:24
Linear Mode
