we have Product relationship info as shown below:


p6 ?/0 [Null or 0]

p5 p6

p4 p5

p3 p4

p2 p3

p1 p2

we need a hierarchy tree as shown below:

leaf level root lvl2 lvl3 lvl4 lvl5 lvl6  lvl7  till lvl20

P1 6 p6 p5 p4 p3 p2 p1

P2 5 P6 p5 p4 p3 p2

P3 4 P6 p5 p4 p3 

P4 3 P6 p5 p4

P5 2 P6 p5

P6 1 P6

level is number of hierarchies that leaf is having

root is the Highest value in the relationship

lvl2 is  in next highest value after root and so on.

we were able to achieve using left joins on parent_id=child_id

leaf level root lvl2 lvl3 lvl4 lvl5 lvl6  lvl7  till lvl20

P1 6 p6 p2 p3 p4 p5 p6

we need in the reverse hier. levels may go till 20.

Any simple solution for this case.


Karthik. N

Senior Supporter

Re: Hierarchies

did you checked recursive queries

I am pretty sure that you can find a solution for your problem with this feature.

Senior Supporter

Re: Hierarchies

I think you can extend to 20 on your own...

create table relation 
(child char(2),
parent char(2)
) primary index (parent);

insert into relation values ('p6',null);
insert into relation values ('p5','p6');
insert into relation values ('p4','p5');
insert into relation values ('p3','p4');
insert into relation values ('p2','p3');
insert into relation values ('p1','p2');

select *
from relation;

with recursive base (leaf, tree_level, root, lv2, lv3, lv4, lv5, lv6, lv7, lv8)
select child as leaf,
1as tree_level,
child as root,
cast(null as char(2)) as lv2,
cast(null as char(2)) as lv3,
cast(null as char(2)) as lv4,
cast(null as char(2)) as lv5,
cast(null as char(2)) as lv6,
cast(null as char(2)) as lv7,
cast(null as char(2)) as lv8
from relation
where parent is null
union all
select i.child as leaf,
b.tree_level+1 as tree_level,
b.root as root,
case when b.tree_level+1 = 2 then i.child else b.lv2 end as lv2,
case when b.tree_level+1 = 3 then i.child else b.lv3 end as lv3,
case when b.tree_level+1 = 4 then i.child else b.lv4 end as lv4,
case when b.tree_level+1 = 5 then i.child else b.lv5 end as lv5,
case when b.tree_level+1 = 6 then i.child else b.lv6 end as lv6,
case when b.tree_level+1 = 7 then i.child else b.lv7 end as lv7,
case when b.tree_level+1 = 8 then i.child else b.lv8 end as lv8
from relation i
base b
on b.leaf = i.parent
select *
from base
order by 1,2

drop table relation;


Re: Hierarchies

Hi Ulrich,


Its really cool, helped me in avoiding many left joins.


Karthik. N

Re: Hierarchies


I have the below hirerchy -


         D2           D3       D4

      D5  D6     D7 D8   D9 D10

 T1 T2 T3 T4  T5 T6 T7 T8 T9 T10 t11

My requirement is to find out currentperm of all the tables but in condition I can only give databasename is D1. Below is the query which is working at one level but not till the end.-

locking row for access


a.DatabaseName AS DatabaseName,

a.TableName AS ObjectName,

a.tablekind AS Tablekind,

CAST (a.CreateTimeStamp AS DATE) AS Create_Date,

sum(b.currentperm)/1024/1024/1024 AS ObjectSize

FROM       dbc.TABLES a left outer join dbc.tablesize b



            and a.tablename=b.tablename


a.databasename in (

sel a.databasename

from dbc.databases a inner join dbc.databases b

on a.databasename=b.databasename

where a.ownername in ( 'D1')


group   by 1,2,3,4

order by 4 desc;