Hello Experts;
I am still trying to learn more about hierarchy and some good tricks. Is this possible.
I have the following sample data below
with sample_info as
(
select 'Transportation' as parent_id, 'Land' as child_id, null as num from dual
union all
select 'Land' as parent_id, 'Cars' as child_id, null as num from dual
union all
select 'Land' as parent_id, 'Bikes' as child_id, null as num from dual
union all
select 'Cars' as parent_id, 'Benz' as child_id, null as num from dual
union all
select 'Benz' as parent_id, 'E_class' as child_id, null as num from dual
union all
select 'E_class' as parent_id, '3_litres' as child_id, 9 as num from dual
union all
select 'Bikes' as parent_id, 'rugged' as child_id, 1 as num from dual
union all
select 'rugged' as parent_id, '3_wheels' as child_id, 3 as num from dual
)
select parent_id ,child_id, num from sample_info
start with parent_id in ('Transportation')
connect by parent_id = prior child_id
The output is the cumulative sum of all the parents at its associated level in the hierarchy.
expected output if possible.... num
Transportation 13
Land 13
Bikes 4
rugged 3
Cars 9
Benz 9
E_Class 9
THanks in advance