By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,557 Members | 1,067 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,557 IT Pros & Developers. It's quick & easy.

recursive queries

P: n/a
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
Share this Question
Share on Google+
2 Replies

P: n/a
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

P: n/a
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.