|
|
|
|
|
|
| Author |
Message |
Jürg Schaufelberger
*nix forums beginner
Joined: 15 Sep 2005
Posts: 4
|
 Posted: Thu Jul 20, 2006 6:06 pm Post subject:
recursive query
|
|
|
Hello ng
A table RECHTGRUNDSTUECK has the following structure:
FID number(10)
FID_BETROFFENESGRUNDSTUECK number(10)
(foreign key of the parent node)
FID_BERECHTIGTESGRUNDSTUECK number(10)
(foreign key of the child)
..
..
Why does the following query only list the first level of the tree ? I
want to list the FID of the root-node and all the leaves. The nodes
between the root and the leaves aren't interesting.
SELECT
RG0.FID_BETROFFENESGRUNDSTUECK, RG0.FID_BERECHTIGTESGRUNDSTUECK
FROM RECHTGRUNDSTUECK RG0
START WITH FID_BETROFFENESGRUNDSTUECK = 534
CONNECT BY PRIOR
FID_BERECHTIGTESGRUNDSTUECK = FID_BETROFFENESGRUNDSTUECK
Oracle Version 9.2
Thanks for any help
Jürg |
|
| Back to top |
|
 |
Charles Hooper
*nix forums addict
Joined: 09 Jul 2006
Posts: 51
|
| Posted: Thu Jul 20, 2006 10:05 pm Post subject:
Re: recursive query
|
|
|
Jürg Schaufelberger wrote:
| Quote: | Hello ng
A table RECHTGRUNDSTUECK has the following structure:
FID number(10)
FID_BETROFFENESGRUNDSTUECK number(10)
(foreign key of the parent node)
FID_BERECHTIGTESGRUNDSTUECK number(10)
(foreign key of the child)
.
.
Why does the following query only list the first level of the tree ? I
want to list the FID of the root-node and all the leaves. The nodes
between the root and the leaves aren't interesting.
SELECT
RG0.FID_BETROFFENESGRUNDSTUECK, RG0.FID_BERECHTIGTESGRUNDSTUECK
FROM RECHTGRUNDSTUECK RG0
START WITH FID_BETROFFENESGRUNDSTUECK = 534
CONNECT BY PRIOR
FID_BERECHTIGTESGRUNDSTUECK = FID_BETROFFENESGRUNDSTUECK
Oracle Version 9.2
Thanks for any help
Jürg
|
I am having a bit of difficulty reading your example. Here is a simple
example from my database. The ACCOUNT table contains a hierarchy:
ID (primary key ID column) and the parent of this ID (PARENT_ACCT_ID
column). If I want to build a hierarchy for this table, I would create
a SQL statement like this (0000 is the root or top PARENT_ACCT_ID for
the table):
SELECT
PARENT_ACCT_ID,
ID,
LEVEL
FROM
ACCOUNT
CONNECT BY PRIOR
ID=PARENT_ACCT_ID
START WITH
PARENT_ACCT_ID='0000';
This produces 509 rows in the result.
If I make a mistake with the connect by prior:
CONNECT BY PRIOR
PARENT_ACCT_ID=ID
START WITH
PARENT_ACCT_ID='0000';
I see only 4 rows in the result. This seems to be somewhat consistent
with your result. Try reversing the order of the columns in the
CONNECT BY PRIOR clause to see if this corrects the problem that you
are experiencing.
Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. |
|
| Back to top |
|
|
Google
|
|
| Back to top |
|
|
|
|
The time now is Mon Dec 01, 2008 8:32 pm | All times are GMT
|
|
Mobile Phone | Israel Perry | Mortgage Calculator | Secured Loans | Credit Cards
|
|
Copyright © 2004-2005 DeniX Solutions SRL
|
|
|
|
Other DeniX Solutions sites:
Unix/Linux blog |
electronics forum |
medicine forum |
science forum |
|
|
Privacy Policy
|
Powered by phpBB © 2001, 2005 phpBB Group
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
Forum index
»
Databases
»
Oracle
»
Server
