Re: last leaf in SQL query

Eh, through some selective removals, I was able to determine that the
performance hit I was taking was due to a couple extra joins for reference
data, which was not shown in the original example. So, performance is
improved by doing a subselect, instead of a join, but I'd still be
interested if there is a better way to get last leaf in a recusrive query
as I'm doing. Said another way, I'm trying to simulate a "where
connect_by isleaf=1' but in a recursive query, rather than a hierarchical
query.

___________________________________
Darren Strong
Dekko





From: "Darren Strong" <darren@xxxxxxxxx>
To: "midrange-l" <midrange-l@xxxxxxxxxxxx>
Date: 10/05/2018 02:29 PM
Subject: last leaf in SQL query
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>



I have a requirement to explode a BOM down to its most basic level
components, meaning, convert all finished goods and subassemblies to raw
onhand qty.


Am I choosing the most efficient way to get this? Its taking quite a
while to run. I'm doing a BOM explosion with a RCTE, and then doing a
"WHERE Child NOT IN" the parent list kind of query to make sure that no FG

or subs are included in the results. I've removed some of the complication

in this example, such as processing the onhand value through the levels to

highlight the point of this question. I started out with "connect by"
logic, but, this doesn't allow multiplication of the qty required down
through the levels, so was unsuitable for my needs here.

If there was a way to tell the query engine what I wanted more directly, I

figured it would run more efficiently, but I don't know if there is a
trick, to again, getting just the last level components and the
accumulated requirement qty.

with Exploded(Parent, Child, Qty, Fac, TopParent) as
(select BPROD, BCHLD, BQREQ, BMWHS, BPROD
from MBML01 MBM
where BMWHS=:DftFac
and :Today8 between BDEFF and BDDIS
union all
select BPROD, BCHLD, BQREQ*Exploded.Qty, Fac, TopParent
from MBML01 mbm
join Exploded
on BPROD=Child
and BMWHS=Fac
where :Today8 between BDEFF and BDDIS

select
L1.part, sum(L2.OnHand)

from IPIM
left outer join
(select TopParent, Child, sum(Qty) as Qty
from Exploded
where Child not in (select Parent from Exploded)
group by TopParent, Child) E1
on E1.TopParent=IPIM.PARTNUM
cross join lateral
(values(case when E1.Child is null then IPIM.PARTNUM
else E1.Child end)) L1(PART)
cross join lateral
(values(case when E1.Child is null then IWI.OnHand
else IWI.OnHand*E1.Qty end)) L2(OnHand)
goup by L1.Part