• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 717
  • Last Modified:

Create hierarchy from tables

I am trying to create a hierarchy of units in the organization.  I have 2 tables  unit table and unit_hierarchy table (attached).  From these tables I need to extract data to a third table called hierarchy_temp.   The hierarchy_temp table is a perfect hierarchy order.   I found a copy of the hierarchy_table table (attached) but do not know how it was created.
Thanks
unit-hierarchy.xls
unit.xls
hierarchy-temp.xls
0
hearnp
Asked:
hearnp
  • 9
  • 7
1 Solution
 
flow01Commented:
Hello hearnp,

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

0
 
hearnpAuthor Commented:
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
0
 
flow01Commented:
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


0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
hearnpAuthor Commented:
I am not sure what you mean by siblings.   I have 2 tables (unit_hierarchy, unit)
pj
0
 
flow01Commented:
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

0
 
hearnpAuthor Commented:
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'
0
 
flow01Commented:
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
/
0
 
hearnpAuthor Commented:
Sorry same results as in orig.xls...
0
 
flow01Commented:
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.
0
 
hearnpAuthor Commented:
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
0
 
flow01Commented:
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
/



0
 
hearnpAuthor Commented:
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
/
0
 
flow01Commented:
is line 9 the one with 'start with upper_unit_id' ?
try

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
/
0
 
hearnpAuthor Commented:
I modified the script slightly,  see below.
It now works great .... Excellent support!!!
0
 
hearnpAuthor Commented:
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
0
 
hearnpAuthor Commented:
Excellent support.
Thanks
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 9
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now