Create hierarchy from tables

Hello hearnp,

where did the solution in the ticket  ID: 24117419
deviate from what your trying to achieve ?

I was able to create your table exactly but the order was different in my original file hierarchy_temp ... see attached (orig.xls)
orig.xls

I did never use it myself :
but try

select unit_id, rownum, level
from hierarchy
connect by prior unit_id = upper_unit_id
start with upper_unit_id = 9999999999
order siblings by unit_id

I am not sure what you mean by siblings.   I have 2 tables (unit_hierarchy, unit)
pj

Extent the original query with
order siblings by unit_id

The results should keep the original hierarchical structure but show the results in a such an order that
all child-units of  parent-unit  are listed in order of unit-id  

I would expect without the order by

unitname      level
unit01           1
   unit03         2
     unit07       3
     unit05       3
   unit02        2
     unit09       3
     unit08       3

and with the order by

unitname      level
unit01           1
   unit02        2
     unit08       3
     unit09       3
   unit03         2
     unit05       3
     unit07       3

unit02 and unit03 are siblings of unit01
unit08 and unit09 are siblings of unit02
unit05 and unit07 are siblings of unit03

The script below  is the script I am running now and I get the results as displayed in file orig.xls that I sent earlier.... but what I should be getting is the same results as in the file hierarchy_temp.xls.
I know this sounds confusing.... I believe that the rank column in the unit table has something to do with. the order.  

select unit_id, rownum , level
from unit_hierarchy
connect by prior unit_id = upper_unit_id
start with upper_unit_id = 9999999999
and unit_id = 'UNT0000294'

try running

select unit_id, rownum , level
from unit_hierarchy
connect by prior unit_id = upper_unit_id
start with upper_unit_id = 9999999999 and unit_id = 'UNT0000294'
order siblings by unit_id
/

Sorry same results as in orig.xls...

I see, I switch both files: it seems to me hierarchy_temp is unsorted.
Or is there a logical rule why UNT0021444 should't be shown before UNT0000310 ?

If the original file was created with no sort order at all you can't always reproduce the results.

I found this powerbuilder script that created the hierarchy_temp table... but I am at a loss as to how to create it... see the file attached

create-hierarchy.txt

The  siblings  in this script are sorted on unit_name from the table unit ,
you didn't include this column  in unit.xls
If it still exist in the table
try  (you need a recent sqlplus version 10G+ for making use of the with clause)

with hierplus
as
(select unit_id, upper_unit_id, unit_name
from unit_hierarchy, unit
where unit.unit_id = unit_hierarchy.unit_id)
select unit_id, rownum , level
from hierplus
connect by prior unit_id = upper_unit_id
start with upper_unit_id = 9999999999 and unit_id = 'UNT0000294'
order siblings by unit_name
/

I get the following error from oracle...
ORA-01722: invalid number at line 9
Below I modified your script to include the alias

 with hierplus
 as
(select uh.unit_id, uh.upper_unit_id, u.unit_name
from unit_hierarchy uh, unit u
where u.unit_id = uh.unit_id)
select unit_id, rownum, level
from hierplus
connect by prior unit_id = upper_unit_id
start with upper_unit_id = 9999999999 and UNIT_ID = 'UNT0000294'
order siblings by unit_name
/

I modified the script slightly,  see below.
It now works great .... Excellent support!!!

Modified script...

with hierplus
 as
(select uh.unit_id, uh.upper_unit_id, u.unit_name, unit_rank
from unit_hierarchy uh, unit u
where u.unit_id = uh.unit_id)
select unit_id, rownum, level, unit_name
from hierplus
connect by prior unit_id = upper_unit_id
start with upper_unit_id = '9999999999' and UNIT_ID = 'UNT0000294'
order siblings by  unit_rank, unit_name

Excellent support.
Thanks