I posted the message below. I just found the way to prefix one-level of
parent:
select a.id,
lpad(' ',4*(level-1))||(select b.name||'/' from test b where b.id =
a.parentid)||a.name name,
a.parentid
from test a
start with a.id = 0
connect by prior a.id = a.parentid;
Since we don't know the maximum depth of parenthood, I can't simply
adding more levels of SELECT to the lpad line. At this moment, I
believe the only way to do what I want is write PL/SQL recursively
traversing the parent tree. Any advice?
Yong H
In article <8cfrrd$8gv$1_at_nnrp1.deja.com>,
yong321_at_yahoo.com wrote:
> Please help me with the following:
>
> create table test (id integer, name varchar2(10), parentid integer);
> insert into test(id, name) values (0, 'top');
> insert into test values (1, 'mid1', 0);
> insert into test values (2, 'mid2', 0);
> insert into test values (3, 'bottom', 1);
>
> col name for a20
> select id, lpad(' ',4*(level-1))||name name, parentid
> from test
> start with id = 0
> connect by prior id = parentid;
>
> The query result is:
> ID NAME PARENTID
> --------- -------------------- ---------
> 0 top
> 1 mid1 0
> 3 bottom 1
> 2 mid2 0
> I'd like to see the second row to be "top/mid1", third
> row "top/mid1/bottom" and last row "top/mid2". I.e., instead of
> lpadding with spaces, I want to pad with parents' (or parents'
parents'
> and so on) names. Is this possible? I can't even get padding one-level
> parent to work, much less all parents. Thanks for help.
>
> --
> Yong Huang
>
> (yong321_at_yahoo.com)
> (http://www.stormloader.com/yonghuang/)
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Wed Apr 05 2000 - 14:57:53 CDT
