recursive queries

[Anand Krishnan]

I have a table

that has 3 columns

folderid parentfolderid 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 parentfolderid whose scope is 1.
In short I want to find the folder id ( parentfolderid ) 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 parent 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 parentfolder id is 0.

Could you help me formulate this query/

[Serge Rielau]

Try this.

WITH rec(id, p_id, scope, cnt)
AS (select folder_id, parent_folder_i d, scope, 1000
from folders where my_folder = folder_id
union all
select folder_id, scope, cnt - 1
from rec, folders where rec.p_id = folders.folder_ id
and rec.scope = 0
and cnt > 0)
select id from rec where scope = 1;

The code assumes that if the input folder id has a scope of 1 it will be
returned right away.
Also I places a recursion limit of 1000. Not needed, but good style.

Cheers
Serge

[Tokunaga T.]

For example:
WITH Recurse (folderid, parentfolderid, scope) AS (
SELECT folderid, parentfolderid, scope
FROM Folder
WHERE folderid = 3
UNION ALL
SELECT new.folderid, new.parentfolde rid, new.scope
FROM Recurse pre
, Folder new
WHERE new.folderid = pre.parentfolde rid
AND pre.scope <> 1
)
SELECT folderid, parentfolderid
FROM Folder
WHERE scope = 1

You may get follwing warning message. You can ignore it.
SQL0347W The recursive common table expression "DB2ADMIN.RECUR SE" may
contain an infinite loop. SQLSTATE=01605