Re: recursive query

Jürg Schaufelberger wrote:
> 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. Received on Thu Jul 20 2006 - 17:05:20 CDT