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/ 2 5586
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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
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 messes
up its variable bindings once in a while. Suddenly, one of
several local variables gets rebound to the object it was bound
to one step up in the recursion.
The relevant part of the code looks like this with print statements
|
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
|
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 NULL". Below is also current
erraneous draft but it has ambigous filed name problem.
CREATE TABLE icategories (
icat_id int(11) NOT NULL auto_increment,
icat_parent_id int(11) default NULL,
icat_name char(100) default NULL,
PRIMARY KEY ...
|
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
|
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 A2
WHERE A1.desc = A2.anc) )
SELECT anc FROM MaryAncestor
| |
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 use pure SQL3 and I cannot use cursors, indexes and the like.
The graph is represented by a set of arcs with the table "arc"
having two attributes "a1" and "a2" for source and target nodes resp.
The SQL3 query I'm using is the following:
|
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 contains
US leg and Europe leg (and others)
US leg contains State tours (and others)
|
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++;)
{
|
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 M1 - Member A1's id changes to M1
M1 D1 - M1 changes to D1
|
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:
StockItemAssemblyID
AssemblyID
StockItemID
Quantity
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |