Home » SQL & PL/SQL » SQL & PL/SQL » Tree Query Output
Tree Query Output [message #256963] |
Tue, 07 August 2007 05:10 |
mudalimuthu
Messages: 64 Registered: May 2005 Location: Bangalore
|
Member |
|
|
This is my tree table...
Please refer the following script....
CREATE TABLE TMAST (PARENT VARCHAR2(50), CHILD VARCHAR2(50));
INSERT INTO TMAST VALUES ('', 'ASSETS');
INSERT INTO TMAST VALUES ('ASSETS', 'FIXED ASSETS');
INSERT INTO TMAST VALUES ('FIXED ASSETS', 'FIXED ASSETS ( GROSS BLOCK )');
INSERT INTO TMAST VALUES ('FIXED ASSETS ( GROSS BLOCK )', 'BUILDING & STORES A/c');
INSERT INTO TMAST VALUES ('BUILDING & STORES A/c', 'BUILDING A/c. (ASSET)');
INSERT INTO TMAST VALUES ('BUILDING & STORES A/c', 'STORES (ASSET) A/c');
INSERT INTO TMAST VALUES ('FIXED ASSETS ( GROSS BLOCK )', 'LAND (ASSET) A/c');
INSERT INTO TMAST VALUES ('LAND (ASSET) A/c', 'LAND (No.413) OLD ASSET');
INSERT INTO TMAST VALUES ('LAND (ASSET) A/c', 'LAND (No.19,20 AT BRINDAVAN ALLOYS) ');
I want the output like the following..
is it possible...
PARENT CHILD
-------------------------------------------------------------------------
ASSETS FIXED ASSETS
ASSETS FIXED ASSETS ( GROSS BLOCK )
ASSETS BUILDING & STORES A/c
ASSETS BUILDING A/c. (ASSET)
ASSETS STORES (ASSET) A/c
ASSETS LAND (ASSET) A/c
ASSETS LAND (No.413) OLD ASSET
ASSETS LAND (No.19,20 AT BRINDAVAN ALLOYS)
FIXED ASSETS FIXED ASSETS ( GROSS BLOCK )
FIXED ASSETS BUILDING & STORES A/c
FIXED ASSETS BUILDING A/c. (ASSET)
FIXED ASSETS STORES (ASSET) A/c
FIXED ASSETS LAND (ASSET) A/c
FIXED ASSETS LAND (No.413) OLD ASSET
FIXED ASSETS LAND (No.19,20 AT BRINDAVAN ALLOYS)
FIXED ASSETS ( GROSS BLOCK ) BUILDING & STORES A/c
FIXED ASSETS ( GROSS BLOCK ) BUILDING A/c. (ASSET)
FIXED ASSETS ( GROSS BLOCK ) STORES (ASSET) A/c
FIXED ASSETS ( GROSS BLOCK ) LAND (ASSET) A/c
FIXED ASSETS ( GROSS BLOCK ) LAND (No.413) OLD ASSET
FIXED ASSETS ( GROSS BLOCK ) LAND (No.19,20 AT BRINDAVAN ALLOYS)
BUILDING & STORES A/c BUILDING A/c. (ASSET)
BUILDING & STORES A/c STORES (ASSET) A/c
LAND (ASSET) A/c LAND (No.413) OLD ASSET
LAND (ASSET) A/c LAND (No.19,20 AT BRINDAVAN ALLOYS)
Or u can find the attachment
thanks in advance
Regards
Muthu
[mod-edit]added code tags.
-
Attachment: TEST.txt
(Size: 2.49KB, Downloaded 568 times)
[Updated on: Tue, 07 August 2007 05:28] by Moderator Report message to a moderator
|
|
|
|
|
|
|
|
Re: Tree Query Output [message #257170 is a reply to message #257006] |
Tue, 07 August 2007 12:38 |
S.Rajaram
Messages: 1027 Registered: October 2006 Location: United Kingdom
|
Senior Member |
|
|
Mudalimuthu,
I will give you many clues. check sys_connect_by_path, instr, substr.
Michael,
I believe he want to display the leaf node as and when he progresses down the tree.
Regards
Rajaram
Michael : I agree, no offense
[Updated on: Tue, 07 August 2007 12:55] Report message to a moderator
|
|
|
|
Re: Tree Query Output [message #257469 is a reply to message #257171] |
Wed, 08 August 2007 07:35 |
mudalimuthu
Messages: 64 Registered: May 2005 Location: Bangalore
|
Member |
|
|
'ASSETS' is parent for 'FIXED ASSETS'
'FIXED ASSETS' is parent for 'FIXED ASSETS (GROSS BLOCK)'
'FIXED ASSETS (GROSS BLOCK)' is parent for 'BUILDING A/c. (ASSET)', 'LAND (ASSET) A/c'
'BUILDING A/c. (ASSET)' is parent for 'BUILDING A/c. (ASSET)', 'STORES (ASSET) A/c'
'LAND (ASSET) A/c' is parent for 'LAND (No.413) OLD ASSET', 'LAND (No.19,20 AT BRINDAVAN ALLOYS) '
this is how we have to establish the link from the bottom level to top level
is it clear.
|
|
|
|
Re: Tree Query Output [message #257506 is a reply to message #256963] |
Wed, 08 August 2007 08:54 |
Cthulhu
Messages: 381 Registered: September 2006 Location: UK
|
Senior Member |
|
|
It's a bit rubbish, but you could do something like this:
select parent, child from
(select substr(full_path, 2, instr(full_path,'~',1,2)-2) as parent,
substr(full_path, instr(full_path, '~', -1)+1, length(full_path) - instr(full_path, '~', -2)) as child from
(
select sys_connect_by_path(child, '~') as full_path
from tmast
start with parent is null
connect by prior child = parent
)
union all
select
substr(full_path, instr(full_path,'~',1,2)+1, instr(full_path,'~',1,3)-instr(full_path,'~',1,2)-1),
substr(full_path, instr(full_path, '~', -2)+1, length(full_path) - instr(full_path, '~', -2)) from
(
select sys_connect_by_path(child, '~') as full_path
from tmast
start with parent is null
connect by prior child = parent
)
union all
select
substr(full_path, instr(full_path,'~',1,3)+1, instr(full_path,'~',1,4)-instr(full_path,'~',1,3)-1),
substr(full_path, instr(full_path, '~', -2)+1, length(full_path) - instr(full_path, '~', -2)) from
(
select sys_connect_by_path(child, '~') as full_path
from tmast
start with parent is null
connect by prior child = parent
)
union all
select
substr(full_path, instr(full_path,'~',1,4)+1, instr(full_path,'~',1,5)-instr(full_path,'~',1,4)-1),
substr(full_path, instr(full_path, '~', -2)+1, length(full_path) - instr(full_path, '~', -2)) from
(
select sys_connect_by_path(child, '~') as full_path
from tmast
start with parent is null
connect by prior child = parent
))
where parent is not null
I can't think of anything simpler than that at the moment (other than to use PL/SQL).
|
|
|
Re: Tree Query Output [message #257514 is a reply to message #256963] |
Wed, 08 August 2007 09:11 |
Cthulhu
Messages: 381 Registered: September 2006 Location: UK
|
Senior Member |
|
|
Slightly neater version:
with source as (select sys_connect_by_path(child, '~') as full_path
from tmast
start with parent is null
connect by prior child = parent)
select parent, child from
(select substr(full_path, 2, instr(full_path,'~',1,2)-2) as parent,
substr(full_path, instr(full_path, '~', -1)+1, length(full_path) - instr(full_path, '~', -2)) as child
from source
union all
select
substr(full_path, instr(full_path,'~',1,2)+1, instr(full_path,'~',1,3)-instr(full_path,'~',1,2)-1),
substr(full_path, instr(full_path, '~', -2)+1, length(full_path) - instr(full_path, '~', -2))
from source
union all
select
substr(full_path, instr(full_path,'~',1,3)+1, instr(full_path,'~',1,4)-instr(full_path,'~',1,3)-1),
substr(full_path, instr(full_path, '~', -2)+1, length(full_path) - instr(full_path, '~', -2))
from source
union all
select
substr(full_path, instr(full_path,'~',1,4)+1, instr(full_path,'~',1,5)-instr(full_path,'~',1,4)-1),
substr(full_path, instr(full_path, '~', -2)+1, length(full_path) - instr(full_path, '~', -2))
from source
union all
select
substr(full_path, instr(full_path,'~',1,5)+1, instr(full_path,'~',1,6)-instr(full_path,'~',1,5)-1),
substr(full_path, instr(full_path, '~', -2)+1, length(full_path) - instr(full_path, '~', -2))
from source
)
where parent is not null
|
|
|
Re: Tree Query Output [message #257569 is a reply to message #257514] |
Wed, 08 August 2007 12:07 |
S.Rajaram
Messages: 1027 Registered: October 2006 Location: United Kingdom
|
Senior Member |
|
|
SQL> l
1 select child, substr(path,2,instr(path,'@',2)-2) as path from
2 (
3 select parent, child, sys_connect_by_path(child,'@') as path, level as my_level from tmast connect by prior parent = child
4* ) where my_level > 1 order by child
SQL> /
CHILD PATH
-------------------------------------------------- --------------------------------------------------
ASSETS LAND (ASSET) A/c
ASSETS BUILDING & STORES A/c
ASSETS LAND (No.19,20 AT BRINDAVAN ALLOYS)
ASSETS LAND (No.413) OLD ASSET
ASSETS STORES (ASSET) A/c
ASSETS FIXED ASSETS ( GROSS BLOCK )
ASSETS BUILDING A/c. (ASSET)
ASSETS FIXED ASSETS
BUILDING & STORES A/c STORES (ASSET) A/c
BUILDING & STORES A/c BUILDING A/c. (ASSET)
FIXED ASSETS LAND (ASSET) A/c
CHILD PATH
-------------------------------------------------- --------------------------------------------------
FIXED ASSETS FIXED ASSETS ( GROSS BLOCK )
FIXED ASSETS BUILDING A/c. (ASSET)
FIXED ASSETS BUILDING & STORES A/c
FIXED ASSETS STORES (ASSET) A/c
FIXED ASSETS LAND (No.413) OLD ASSET
FIXED ASSETS LAND (No.19,20 AT BRINDAVAN ALLOYS)
FIXED ASSETS ( GROSS BLOCK ) LAND (No.413) OLD ASSET
FIXED ASSETS ( GROSS BLOCK ) STORES (ASSET) A/c
FIXED ASSETS ( GROSS BLOCK ) BUILDING A/c. (ASSET)
FIXED ASSETS ( GROSS BLOCK ) LAND (ASSET) A/c
FIXED ASSETS ( GROSS BLOCK ) BUILDING & STORES A/c
CHILD PATH
-------------------------------------------------- --------------------------------------------------
FIXED ASSETS ( GROSS BLOCK ) LAND (No.19,20 AT BRINDAVAN ALLOYS)
LAND (ASSET) A/c LAND (No.413) OLD ASSET
LAND (ASSET) A/c LAND (No.19,20 AT BRINDAVAN ALLOYS)
25 rows selected.
|
|
|
|
Goto Forum:
Current Time: Thu Dec 12 23:37:18 CST 2024
|