Heirarchical Queries
Has anyone tried using the prior operator for hierarchical queries?
A classic requirement is a Bill of Materials traversal, calculating required
qtys at each child node as the tree is traversed.
I crated a simple BOM in my stores db:
drop table if exists bom;
create table bom(
row_id serial,
parent varchar(20),
child varchar(20),
qty decimal(14,4),
uom char(4));
insert into bom (parent,child,qty,uom) values ('BIKE','FRAME',1,'EA');
insert into bom (parent,child,qty,uom) values ('BIKE','WHEEL',2,'EA');
insert into bom (parent,child,qty,uom) values ('WHEEL','SPOKE',28,'EA');
insert into bom (parent,child,qty,uom) values ('WHEEL','RIM',1,'EA');
insert into bom (parent,child,qty,uom) values ('WHEEL','TYRE',1,'EA');
insert into bom (parent,child,qty,uom) values ('WHEEL','TUBE',1,'EA');
insert into bom (parent,child,qty,uom) values ('WHEEL','VALVE',1,'EA');
insert into bom (parent,child,qty,uom) values ('FRAME','MASTERFRAME',1,'EA');
insert into bom (parent,child,qty,uom) values ('FRAME','SADDLE',1,'EA');
insert into bom (parent,child,qty,uom) values ('FRAME','HBAR',1,'EA');
insert into bom (parent,child,qty,uom) values ('HBAR','MAINHBAR',1,'EA');
insert into bom (parent,child,qty,uom) values ('HBAR','GRIPS',2,'EA');
insert into bom (parent,child,qty,uom) values ('HBAR','GEARLEVER',1,'EA');
insert into bom (parent,child,qty,uom) values ('HBAR','BRAKELEVER',2,'EA');
insert into bom (parent,child,qty,uom) values ('HBAR','BRAKECABLE',3,'MTR');
insert into bom (parent,child,qty,uom) values ('FRAME','PEDAL',2,'EA');
insert into bom (parent,child,qty,uom) values ('FRAME','GEAR ',6,'EA');
insert into bom (parent,child,qty,uom) values ('FRAME','CHAIN',1.5,'MTR');
insert into bom (parent,child,qty,uom) values ('PEDAL','CRANK',1,'EA');
insert into bom (parent,child,qty,uom) values ('PEDAL','FOOTPLATE',1,'EA');
insert into bom (parent,child,qty,uom) values ('PEDAL','REFLECTOR',2,'EA');
insert into bom (parent,child,qty,uom) values ('SPOKE','ROD',1,'EA');
insert into bom (parent,child,qty,uom) values ('SPOKE','NUT',2,'EA');
Then I created a traversal query:
select
t0.parent,
lpad( trim(t0.child),level + len(t0.child),'-')::varchar(15) as child,
t0.qty
from
bom t0
start with parent = 'BIKE'
connect by nocycle prior child = parent
parent child qty
-------------------- --------------- ----------------
BIKE -WHEEL 2.0000
WHEEL --VALVE 1.0000
WHEEL --TUBE 1.0000
WHEEL --TYRE 1.0000
WHEEL --RIM 1.0000
WHEEL --SPOKE 28.0000
SPOKE ---NUT 2.0000
SPOKE ---ROD 1.0000
BIKE -FRAME 1.0000
FRAME --CHAIN 1.5000
FRAME --GEAR 6.0000
FRAME --PEDAL 2.0000
PEDAL ---REFLECTOR 2.0000
PEDAL ---FOOTPLATE 1.0000
PEDAL ---CRANK 1.0000
FRAME --HBAR 1.0000
HBAR ---BRAKECABLE 3.0000
HBAR ---BRAKELEVER 2.0000
HBAR ---GEARLEVER 1.0000
HBAR ---GRIPS 2.0000
HBAR ---MAINHBAR 1.0000
FRAME --SADDLE 1.0000
FRAME --MASTERFRAME 1.0000
So far so good, but how do I work out that 4 reflectors are required (or 28
rods and 56 nuts for the spokes?)
The manual seems to suggest that you can use the PRIOR operator in the
projection clause eg:
select
t0.parent,
lpad( trim(t0.child),level + len(t0.child),'-')::varchar(15) as child,
t0.qty * prior t0.qty
from
bom t0
start with parent = 'BIKE'
connect by nocycle prior child = parent
but this causes a syntax error.
How are you supposed to flow the quantitys through the bill?
I finally worked it out so I'm putting the post here in case it helps anyone
else in the future. The answer is to sum the logs of the qty in a subselect:
select
'Bike' || sys_connect_by_path(trim(child),'/')::char(30) path,
-- The following subselect is the key:
(select exp(sum(logn(qty)))
from
(
select t2.qty
from bom t2
start with t2.child = t0.child
connect by nocycle prior t2.parent = t2.child
)
) as totalqty
from
bom t0
start with t0.parent = 'BIKE'
connect by nocycle prior t0.child = t0.parent
path totalqty
---------------------------------- -----------------
Bike/WHEEL 2
Bike/WHEEL/VALVE 2
Bike/WHEEL/TUBE 2
Bike/WHEEL/TYRE 2
Bike/WHEEL/RIM 2
Bike/WHEEL/SPOKE 56
Bike/WHEEL/SPOKE/NUT 112
Bike/WHEEL/SPOKE/ROD 56
Bike/FRAME 1
Bike/FRAME/CHAIN 1.5
Bike/FRAME/GEAR 6
Bike/FRAME/PEDAL 2
Bike/FRAME/PEDAL/REFLECTOR 4
Bike/FRAME/PEDAL/FOOTPLATE 2
Bike/FRAME/PEDAL/CRANK 2
Bike/FRAME/HBAR 1
Bike/FRAME/HBAR/BRAKECABLE 3
Bike/FRAME/HBAR/BRAKELEVER 2
Bike/FRAME/HBAR/GEARLEVER 1
Bike/FRAME/HBAR/GRIPS 2
Bike/FRAME/HBAR/MAINHBAR 1
Bike/FRAME/SADDLE 1
Bike/FRAME/MASTERFRAME 1
Ray