Hierarchical Query

From: Fei Sha <maile_at_altavista.net>
Date: Fri, 22 Jan 1999 13:39:38 +0100
Message-ID: <36A8718A.DF402429@altavista.net>

Hi, everybody,

  I want to do something with hierarchical query, but I don't know how to. And I wonder whether it is possible after all.

  Here is a table:

TABLE T

---

parent_id name child_id

---

1		A	2
2		B	3
3		C	4
4		D	5

...

---

I want a query to return the trace of the hierarchy.

i.e. A.B.C.D

If I use:

SELECT (prior name || ' ' || name) as name FROM t
START WITH parent_id = 1
CONNCET BY prior child_id = parent_id

The result is:

A
A B
B C
C D

The "prior name" is the name field in the original table rather than the "name" defined in the select clause.

If I use:

SELECT (prior longname || ' ' || name) as longname FROM t
START WITH parent_id = 1
CONNCET BY prior child_id = parent_id  

Then it reports "longname" is invalid column name, (which is true when level = 1)

Can somebody tell me whether it is possible to do what I want to or, if yes, how.

Thanks!

Fei.

PS: urgent... Received on Fri Jan 22 1999 - 06:39:38 CST