Re: SQL-statement - BOM problem

You need two things to do this - an "IsLeaf" function that the other
poster mentioned, plus a way to recursively compute the QTY field for a
part - e.g., if you had 50 spokes/wheel, you'd want the QTY row to be
100, not 50.

The IsLeaf function is relatively easy, even if you're not using 10g:

SELECT Child
, CASE
WHEN (LEAD(LEVEL)
OVER (PARTITION BY 1 ORDER BY Rownum)
) >= LEVEL
THEN 'Node'
ELSE 'Leaf'
END IsLeaf

The "Lead" function returns the value from the next row in the query -
what we're doing here is saying "If the LEVEL value from this row is
less than the value of LEVEL from the next row, we must be a non-leaf
node". The "OVER" part is required for Analytic Functions like LEAD -
check the SQL Reference manual for more info on it. In our case here,
I'm saying to partition by the constant 1 (which is to say, don't
partition at all - treat the whole result set as our group to analyze)
and order by Rownum (which means analyze the data in the order it is
returned by the query itself).

As for the recursive QTY value, you're just going to have to write a
PL/SQL function to compute it - I don't know of any way to keep track
of the most recent value of a column for a particular LEVEL value in a
hierarchical query - maybe another analytic expert will have more
insight.

The tricky part has to do with your schema: The function will have to
accept the PK of the current part you're on, and traverse up the tree,
multiplying QTY while it traverses. But in your example you have two
entries for "wheel" - car-wheel and bike-wheel. If that's the case,
how do I know how many rims to include if I'm on a rim row - 2 or 4
(rim-wheel-bike or rim-wheel-car). The only way for this to work is if
there are two different "rims" in your system, depending on if it's one
that ends up on a bike or a car.

However, I assume that it is possible to have two completely different
products that have different quantities of the same complex part (for
example, you may have a "pedal" object of which you have 2 on a regular
bike and 4 on a tandem bike). That makes thing much more complicated
for our function - we can no longer just traverse up the tree,
multiplying QTYs until reach the root, because there will be multiple
places in the tree where a "pedal" connects to it's parent component.

Instead, what we'll need to do is keep a running record of what the
last QTY we saw was for a given level, since for any given leaf at
LEVEL N, the QTY will be QTY(LEVEL N) * QTY(LEVEL N-1) * QTY(LEVEL N-2)
.... QTY(LEVEL 1). Probably the easiest way to do this would be to
create a package with an array as a data member, and use this to keep
the last seen value of a QTY for a given LEVEL. A variable declared in
a package will maintain an independent value on a per-session basis, so
you don't need to worry about two sessions executing this code at the
same time. With the function in it to compute the QTY product, we'd
get this:

CREATE OR REPLACE PACKAGE BOMUtility_PKG
AS
TYPE LEVEL_QTY_TYPE IS VARRAY(255) OF INTEGER;

LEVEL_QTY LEVEL_QTY_TYPE := LEVEL_QTY_TYPE(255);

FUNCTION SetLevelQTY
( TheLEVEL NUMBER
, QTY NUMBER)
RETURN NUMBER;

FUNCTION ComputeLeafQTY
( TheLEVEL NUMBER
, QTY NUMBER)
RETURN NUMBER;

END BOMUtility_PKG;
/

CREATE OR REPLACE PACKAGE BODY BOMUtility_PKG
AS
FUNCTION SetLevelQTY
( TheLEVEL NUMBER
, QTY NUMBER)
RETURN NUMBER
AS
BEGIN
BEGIN
LEVEL_QTY(TheLEVEL) := QTY;
EXCEPTION
WHEN SUBSCRIPT_BEYOND_COUNT THEN
LEVEL_QTY.EXTEND;
LEVEL_QTY(TheLEVEL) := QTY;
END;
RETURN 0;
END;

FUNCTION ComputeLeafQTY
( TheLEVEL NUMBER
, QTY NUMBER)
RETURN NUMBER
AS
BEGIN
DECLARE
CurrentLevel NUMBER := TheLevel;
CurrentQTY NUMBER := QTY;
BEGIN
WHILE CurrentLevel > 1 LOOP
CurrentLevel := CurrentLevel - 1;
CurrentQTY := CurrentQTY * LEVEL_QTY(CurrentLevel);
END LOOP;

RETURN CurrentQTY;
END;
END;

END BOMUtility_PKG;
/

This package isn't exactly bulletproof - you will get an error if you
call ComputeLeafQTY for a level and you haven't called SetLevelQTY for
all previous levels - you'll want to handle your errors accordingly.
You'll also have to decide what value to use for the "255" in the code
above - this number must be larger than the deepest part of your parts
tree.

So, now with the package above, and the logic at the beginning, you can
put the two together, and add an enclosing select to get rid of all the
rows that have a zero value for QTY (which we will get for non-leaf
nodes, thanks to the fact that we made the SetLevelQTY function always
return 0):

SELECT *
FROM (
SELECT Child
, CASE
WHEN (LEAD(LEVEL)
OVER (PARTITION BY 1 ORDER BY Rownum)
) >= LEVEL
THEN BOMUTILITY_PKG.SetLevelQTY(LEVEL, QTY) -- Node
ELSE BOMUTILITY_PKG.ComputeLeafQTY(LEVEL, QTY) -- Leaf
END QTY
FROM BOMTable
START WITH Parent = 'your product here'
CONNECT BY PRIOR Child = Parent
)
WHERE QTY > 0;

-Steve

.

Relevant Pages