CODEWITHSUNDEEP
sqloracle-database
travBoog
travBoog

Reputation: 73

Conditional sum roll up of hierarchical table in Oracle?

I've ready through tons forum posts and Ask Tom pages and can't seem to find anything that works so I'm hoping you can help me out. I have a table I've created that has hierarchical data. I have the parent part number, parent price, child part number, child price, child quantity, level in the hierarchy.

Parent price always takes precedence over the sum of children - however I need to backfill where we have gaps. This is built from a root arrangement number so all level 1's are considered top level arrangements with a quantity of 1 that I need to roll up to.

I have two problems. 1) If the parent part number has a price and is also a component I need parent price x cmpnt qty to get total price 2) If the parent price is missing and all/any of the children have a price I need to "backfill" the parent price with this calculation. If only some of the children have a price I need to keep going further down the tree until I can backfill the parent.

I wrote a query but it sums all levels and my numbers where huge. I need to stop once I've successfully backfilled a level.

This is on Oracle 12C Enterprise.

Here is my test table:

--DROP TABLE cost_rollup;

CREATE TABLE cost_rollup
(
 parent_no VARCHAR2 (4 BYTE),
 parent_prc NUMBER,
 child_no VARCHAR2 (4 BYTE),
 child_prc NUMBER,
 child_qty NUMBER,
 level_ NUMBER
);

My data:

INSERT INTO cost_rollup
 VALUES ('1A1',NULL, '2A1', 0.35, 4, 1);

INSERT INTO cost_rollup
 VALUES ('1A1', NULL, '2A2', 1, 2, 1);

INSERT INTO cost_rollup
 VALUES ('1A1', NULL, '2A3', 1.25, 1, 1);

INSERT INTO cost_rollup
 VALUES ('1A2', 3, '2A4', 0.27, 1, 1);

INSERT INTO cost_rollup
 VALUES ('1A2', 3, '2A5', 0.3, 2, 1);

INSERT INTO cost_rollup
 VALUES ('1A3', NULL, '2A6', 25, 1, 1);

INSERT INTO cost_rollup
 VALUES ('1A3', NULL, '2A7', 2, 2, 1);

INSERT INTO cost_rollup
 VALUES ('1A4', NULL, '2A8', NULL, 2, 1);

INSERT INTO cost_rollup
 VALUES ('2A8', NULL, '3A1', 3, 1, 2);

INSERT INTO cost_rollup
 VALUES ('2A8', NULL, '3A2', 2, 2, 2);

INSERT INTO cost_rollup
 VALUES ('1A5', NULL, '2A9', 0.5, 4, 1);

INSERT INTO cost_rollup
 VALUES ('1A5', NULL, '2A10', NULL, 1, 1);

INSERT INTO cost_rollup
 VALUES ('2A10', NULL, '3A3', 2, 5, 2);

 COMMIT;

Output: Select * from cost_rollup;

PARENT_NO   PARENT_PRC  CHILD_NO    CHILD_PRC   CHILD_QTY   LEVEL_
1A1                     2A1          0.35        4          1
1A1                     2A2          1           2          1
1A1                     2A3          1.25        1          1
1A2         3           2A4          0.27        1          1
1A2         3           2A5          0.3         2          1
1A3                     2A6          25          1          1
1A3                     2A7          2           2          1
1A4                     2A8                      2          1
2A8                     3A1          3           1          2
2A8                     3A2          2           2          2
1A5                     2A9          0.5         4          1
1A5                     2A10                     1          1
2A10                    3A3          2           5          2

My expected rollup would be something like:

Parent          parent_price      level
1A1                4.65           1
1A2                3.00           1
1A3                29.00          1  
1A4                14.00          1 
2A8                7.00           2
1A5                12.00          1
2A10               10.00          2

EDIT: Here is the query I've been working with

WITH b
     AS (    SELECT CONNECT_BY_ROOT child_no root,
                    child_no,
                    parent_no,
                    level_,
                    parent_prc,
                    child_prc * child_qty AS VALUE
               FROM cost_rollup
         START WITH parent_no = '1A1'
         CONNECT BY parent_no = PRIOR child_no)
  SELECT parent_no,
     CASE
        WHEN parent_prc IS NOT NULL THEN AVG (parent_prc)
        ELSE SUM (VALUE)
     END
        prc
FROM b
GROUP BY parent_no, parent_prc

Upvotes: 2

Views: 1659

Answers (2)

Conrad Frix
Conrad Frix

Reputation: 52675

At first glance this seems like a fairly standard Connect_by_root and sum.

However the tricky part of your problem is 1A4 because you need to multiply the quantity of 2 by the children component values. SUM(COALESCE(PRIOR_CHILD_QTY,1) * CHILD_QTY *CHILD_PRC) solves that. 

with Flattened as (
select
   connect_by_root parent_no rootParent,  
   PARENT_PRC,
   CHILD_PRC,
   CHILD_QTY,
   prior CHILD_QTY prior_CHILD_QTY,
   level lvl


 FROM
   cost_rollup
   connect by prior child_no = parent_no
)

SELECT
  rootParent Parent,
  COALESCE(MIN(PARENT_PRC),
        SUM(COALESCE(PRIOR_CHILD_QTY,1) * CHILD_QTY *CHILD_PRC)) parent_price,
        MAX(lvl) as "Level"
FROM
    Flattened
Group by rootParent
ORDER BY rootParent

Demo

Upvotes: 1

Hotdin Gurning
Hotdin Gurning

Reputation: 1819

You might try this :

SELECT parent_no,sum(parent_price) parent_price,level_
FROM(
        SELECT parent_no,
            (child_qty * child_prc) parent_price,level_
        FROM cost_rollup
    ) t
GROUP BY parent_no,level_

Upvotes: 0

Related Questions