Re: SQL-statement - BOM problem

From: <BigBoote66_at_hotmail.com>
Date: 29 Sep 2005 12:50:14 -0700
Message-ID: <1128023413.966277.250420@g47g2000cwa.googlegroups.com>

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 Received on Thu Sep 29 2005 - 14:50:14 CDT