473,320 Members | 1,876 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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
2 5556
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.

Cheers
Serge
Nov 12 '05 #2
For example:
WITH Recurse (folderid, parentfolderid, scope) AS (
SELECT folderid, parentfolderid, scope
FROM Folder
WHERE folderid = 3
UNION ALL
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Magnus Lyck? | last post by:
Something really strange is happening to me (sometimes). I'm using Python 2.3.2 on NT 4.0 as well as win32all-157, adodbapi and db_row. During a recursive call to a method, it seems Python...
9
by: JP SIngh | last post by:
Hi All I am trying to write a recursive function to list the managers and his employees in a tree like sctructure Manager 1 Emp1 Emp1.1 Emp 1.2 Emp 2
2
by: Perttu Pulkkinen | last post by:
I need to find toplevel image categories and a) number of images directly in them and b) number of subcategories directly in those topcategories. In toplevel image categories "icat_parent_id IS...
2
by: Steven Burn | last post by:
..:: The Specs: MS Access 2000 (host charges extra for SQL/MySQL) MS Windows Server 2003 (prod) / MS XP SP1 (dev) ..:: The setup: The database has been setup with two tables; tblDownloads
12
by: Mikito Harakiri | last post by:
I wonder if WITH RECURSIVE MaryAncestor(anc,desc) AS ( (SELECT parent as anc, child as desc FROM ParentOf WHERE desc = "Mary") UNION (SELECT A1.anc, A2.desc FROM MaryAncestor A1, MaryAncestor...
3
by: Vincenzino | last post by:
Hi, I have some problem in using SQL3 recursive queries on DB2 database system (8.1 and 8.2 UDB). I need to compute the transitive closure of a (possibly) ciclic graph using SQL3 on DB2. I MUST...
25
by: Mike MacSween | last post by:
Regular viewers may want to turn off now. This will be an orchestral management system. Musicians and other staff being booked/paid for jobs. A job may contain other jobs, e.g: World Tour...
4
by: so.intech | last post by:
for example, ret = 0; for(i=0; i<3; i ++;) { for(j=0; j<4; j++;) { for(k=0; k<3; k++;) { for(m=0; m<4; m++;) {
3
by: NatRoger | last post by:
Hi, We are using DB2 V7 (MVS) in our shop, which dont have the luxury of the 'WITH clause' - CTE support to accomplish recursive queries. Here is my problem - Table1 From-ID To-ID A1 ...
2
by: Jim Devenish | last post by:
I have a table named StockItems with field: StockItemID QuantityInStock I am creating assemblies of stock items and have another table named StockItemAssemblies with fields:...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.