I have a simple table that is used to build up a tree structure in an application. Then there are related objects loacted in a diferent table. In the application I can see the tree similar to a directory structure. Now, I want to be able to examine the tree from SQL in order to find large objects. I can do this in a query and get the objects ordered by size. The problem I have is that witnh my query, I'm not able to determine in what path these objects are located.
The table I have has the fields: NODE, NAME, CHILDNODE where NODE and CHILDNODE are integer ID numbers and NAME is a varchar2.
If I do something like:
select * from ossdnodes start with inode=1 connect by childinode=inode
I will get the first level of the tree structure. But I would like to be able to get all in one go and then bring in some other information about the objects like size and owner from another table.
I think I need to use the WITH statement but I haven't been able to figure this out.
The desired output would be something like:
LEVEL1 > LEVEL2 > LEVEL3 NAME
(Formatting is not critical but a path separator looks nicer.)
Please just let me know if the question is not clear.