SQL & PL/SQL

Hi,

I am working on a hierarchical query, using connect by prior. Each node in the tree has two properties, an type and a sequence.

The table that contains the hierarchy has 4 fields:
element_type_from
element_sequence_from
element_type_to
element_sequence_to

Each child has one parent, a parent can have multiple childeren. For a leaf, the fields element_type_to and element_sequence_to are null.

To generate a tree you can run:

select element_type_to
,      element_sequence_to
from   element_hierarchy
start with element_type_from = [root_element_type]
       and element_sequence_from = [root_element_sequence]
connect by prior element_type_to = element_type_from
       and prior element_sequence_to = element_sequence_from

That works fine... however... not only do I want the child elements, I would like to return the root element sequence for each child (in our table the root element type is always the same). There are multiple root elements and I want to create a list containing all trees and each node in the tree must have its root as well.

There is the option of using sys_connect_by_path. This returns the root, but also the entire path to the current child. Also it returns a varchar2, that needs to be substr-ed and to_number-ed to get to the sequence... not nice.

warning, extremely ugly (but functional) code:

select element_type_to
,      element_sequence_to
,      to_number(substr(sys_connect_by_path(element_sequence_from ,','),2,instr(sys_connect_by_path(element_sequence_from ,',') ||',',',',2)-2)) root_sequence
from   element_hierarchy
start with element_type_from = [root_element_type]
       and element_sequence_from = ( select [root_element_sequence] from all_root_elements )
connect by prior element_type_to = element_type_from
       and prior element_sequence_to = element_sequence_from

There has to be a simple solution that I am missing here! Can you help?

Edit: Oops, database version is 10.2.0.4.0