OraFAQ Forum: SQL & PL/SQL » Tree Query Output

Home » SQL & PL/SQL » SQL & PL/SQL » Tree Query Output

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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 #256968 is a reply to message #256963]

Tue, 07 August 2007 05:15

Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Tree Query Output [message #256972 is a reply to message #256968]

Tue, 07 August 2007 05:19

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..

...

Report message to a moderator

Re: Tree Query Output [message #256979 is a reply to message #256972]

Tue, 07 August 2007 05:28

Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

The whole path or only the first parent and the leaves or all the possible combinations?

Regards
Michel

Report message to a moderator

Re: Tree Query Output [message #256982 is a reply to message #256979]

Tue, 07 August 2007 05:33

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..

Report message to a moderator

Re: Tree Query Output [message #257006 is a reply to message #256982]

Tue, 07 August 2007 06:53

Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...

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

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 #257171 is a reply to message #257170]

Tue, 07 August 2007 12:46

Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...

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

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.

Report message to a moderator

Re: Tree Query Output [message #257491 is a reply to message #257469]

Wed, 08 August 2007 08:27

Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

It is clear what you have, it is not clear what you want.

Regards
Michel

Report message to a moderator

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).

Report message to a moderator

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

Report message to a moderator

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.

Report message to a moderator

Re: Tree Query Output [message #258069 is a reply to message #257569]

Fri, 10 August 2007 01:24

mudalimuthu
Messages: 64
Registered: May 2005
Location: Bangalore

Member

Thanks a lot Rajaram ...

its working fine....

this is what i wanted exactly ...

Report message to a moderator

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Dec 12 23:37:18 CST 2024