467,118 Members | 1,043 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,118 developers. It's quick & easy.

recursive queries

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/
Nov 12 '05 #1
  • viewed: 5296
2 Replies
Try this.

WITH rec(id, p_id, scope, cnt)
AS (select folder_id, parent_folder_id, 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.

Nov 12 '05 #2
For example:
WITH Recurse (folderid, parentfolderid, scope) AS (
SELECT folderid, parentfolderid, scope
FROM Folder
WHERE folderid = 3
SELECT new.folderid, new.parentfolderid, new.scope
FROM Recurse pre
, Folder new
WHERE new.folderid = pre.parentfolderid
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.RECURSE" may
contain an infinite loop. SQLSTATE=01605
Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

9 posts views Thread by JP SIngh | last post: by
2 posts views Thread by Perttu Pulkkinen | last post: by
25 posts views Thread by Mike MacSween | last post: by
3 posts views Thread by NatRoger | last post: by
2 posts views Thread by Jim Devenish | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.