Home » SQL & PL/SQL » SQL & PL/SQL » Tree Query Output
Tree Query Output [message #256963] Tue, 07 August 2007 05:10 Go to next message
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 234 times)

[Updated on: Tue, 07 August 2007 05:28] by Moderator

Report message to a moderator

Re: Tree Query Output [message #256968 is a reply to message #256963] Tue, 07 August 2007 05:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Make sure that lines of code do not exceed 80 or 100 characters when you format.
Please always post your Oracle version (4 decimals).

Please say with WORDS what the output should be, we don't have time to reverse engineer your output.

Regards
Michel
Re: Tree Query Output [message #256972 is a reply to message #256968] Tue, 07 August 2007 05:19 Go to previous messageGo to next message
mudalimuthu
Messages: 64
Registered: May 2005
Location: Bangalore
Member
for each and every parent i want all the childs/sub childs belongs to those parent items..

...
Re: Tree Query Output [message #256979 is a reply to message #256972] Tue, 07 August 2007 05:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The whole path or only the first parent and the leaves or all the possible combinations?

Regards
Michel
Re: Tree Query Output [message #256982 is a reply to message #256979] Tue, 07 August 2007 05:33 Go to previous messageGo to next message
mudalimuthu
Messages: 64
Registered: May 2005
Location: Bangalore
Member
The whole path...

I think my output requirement will gives you clear picture.. if i am not wrong..

Re: Tree Query Output [message #257006 is a reply to message #256982] Tue, 07 August 2007 06:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Your output is not formatted, I can't read it.
Now, for instance, this line:
ASSETS                                LAND (No.413) OLD ASSET

is not the full path but only then first parent and one leaf.
This one:
ASSETS                                FIXED ASSETS

Is the first parent and a node.
This one:
FIXED ASSETS                          FIXED ASSETS ( GROSS BLOCK )

Is an intermediate node and a leaf.
None is a whole path.

How is this clearly states what you want?

Regards
Michel



Re: Tree Query Output [message #257170 is a reply to message #257006] Tue, 07 August 2007 12:38 Go to previous messageGo to next message
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 #257171 is a reply to message #257170] Tue, 07 August 2007 12:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I believe he want to display the leaf node as and when he progresses down the tree.

Maybe but it is HIS problem so HE has to say what he wants.

Regards
Michel
Re: Tree Query Output [message #257469 is a reply to message #257171] Wed, 08 August 2007 07:35 Go to previous messageGo to next message
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 #257491 is a reply to message #257469] Wed, 08 August 2007 08:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is clear what you have, it is not clear what you want.

Regards
Michel
Re: Tree Query Output [message #257506 is a reply to message #256963] Wed, 08 August 2007 08:54 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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.
Re: Tree Query Output [message #258069 is a reply to message #257569] Fri, 10 August 2007 01:24 Go to previous message
mudalimuthu
Messages: 64
Registered: May 2005
Location: Bangalore
Member
Thanks a lot Rajaram ...

its working fine....

this is what i wanted exactly ...


Previous Topic: diff on numeric null and char null in unique constraint
Next Topic: How to get the 7years back date based on the sysdate???
Goto Forum:
  


Current Time: Fri Dec 09 02:00:07 CST 2016

Total time taken to generate the page: 0.10715 seconds