Forum Stats

  • 3,734,025 Users
  • 2,246,861 Discussions
  • 7,857,001 Comments

Discussions

Rollup Hierarchy SQL

795486
795486 Member Posts: 16
edited March 2013 in SQL & PL/SQL
We are trying to create a report with SQL that rolls up a Bill of Material and are having issues. We are using the connect_by and start_with function but the total cost is not being multiplied by the parent quantity.

Does anyone have any experience with this?

Example Data_
*Part No     Parent Part No      Quantity           Cost                                      Total Unit Cost*
A              Null                  2              Blank                                       Blank
 B             A                     3              Blank                                       Blank
  C            B                     4              Blank                                       Blank
   D           C                     2              $10                                          $20
   E           C                     1              $5                                           $5
Expected Results_
*Part No   Parent Part No        Quantity           Cost                                        Total Unit Cost*
A              Null                  2          Derived from sum of children ($300)                 $600 ($300 x 2)
 B              A                    3          Derived from sum of children ($100)                 $300 ($100 x 3)
  C             B                    4          Derived from sum of children ($25)                  $100 ($25 x 4)            
   D            C                    2          $10                                                 $20
   E            C                    1           $5                                                 $5
CASE WHEN total_unit_cost IS NULL THEN
       quantity *  (SELECT SUM(total_cost)
           FROM (SELECT part_no, 
                        parent_part_no,
                        total_unit_cost * quantity total_cost
                   FROM mce.GDAIS_PROPOSAL_HEADER_V    a,
                        mce.GDAIS_PROPOSAL_HIERARCHY_V b
          WHERE      
             a.PROPOSAL_NO = 'TKPRICE5' AND
          CONNECT BY PRIOR aa.part_no = aa.parent_part_no
                 START WITH aa.parent_part_no = c.part_no

Answers

  • Centinul
    Centinul Member Posts: 6,871
    edited September 2010
    Welcome to the forums!

    The best way to get results if you provide sample data in a form that is easy for the forum members to use. If you can convert your sample data to CREATE / INSERT statements chances are you'll get a response much faster.

    I drafted up the sample data:
    WITH t_data AS
    (
            SELECT 'A' AS part_no, NULL AS parent_part_no, 2 AS quantity, NULL AS cost FROM DUAL UNION ALL
            SELECT 'B' AS part_no, 'A' AS parent_part_no, 3 AS quantity, NULL AS cost FROM DUAL UNION ALL
            SELECT 'C' AS part_no, 'B' AS parent_part_no, 4 AS quantity, NULL AS cost FROM DUAL UNION ALL
            SELECT 'D' AS part_no, 'C' AS parent_part_no, 2 AS quantity, 10 AS cost FROM DUAL UNION ALL
            SELECT 'E' AS part_no, 'C' AS parent_part_no, 1 AS quantity, 5 AS cost FROM DUAL
    ) 
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,497 Red Diamond
    Hi,

    Welcome to the forum!

    In addition to the information that Centinul mentioned, you should also post the version of Oracle you are using. This is especially important with CONNECT BY queries: every version since Oracle 7 has had significant improvements in CONNECT BY capabilities.
    In particular, recursive WITH clauses, introduced in Oracle 11.2, make this job a lot easier. The problem here is that we have to know the total_cost for children before we can compute the total_cost for a parent. Without recursive sub-queries, that either requires a MODEL clause, dynamic SQL (that is, a function that can take a string like '4 * ((1*5) + (2*10))' and return 100), or some XML magic. None are trivial, and people might not be willing to go to the trouble of writing and testing a solution if they have no idea whether it will help upu or not because you don't have the right version of Oracle.
  • 795486
    795486 Member Posts: 16
    Thanks for help so far. As i am new to the forum, i'm still learning what is needed for a good post.

    We are using Oracle 10gR2 (10.2.0.4).
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,497 Red Diamond
    Hi,

    This gets the results you requested form the sample data Centinul posted:
    WITH	universe	AS
    (
    	SELECT	part_no
    	,	LPAD ( ' '
    	    	      , 2 * (LEVEL - 1)
    	     	      ) || part_no		AS indented_part_no
    	,	parent_part_no
    	,	quantity
    	,	cost
    	,	ROWNUM				AS r_num
    	FROM	t_data
    	START WITH	part_no		= 'A'
    	CONNECT BY	parent_part_no	= PRIOR part_no
    )
    ,	bottom_up	AS
    (
    	SELECT	u.*
    	,	LPAD ( '('
    		     , LEVEL
    		     , '('
    		     )  || CONNECT_BY_ROOT	 TO_CHAR (cost)
    		     	|| SYS_CONNECT_BY_PATH ( TO_CHAR (quantity)
    				    	       , ') * '
    				    	       )		AS cost_string
    	FROM	universe	u
    	START WITH	cost	IS NOT NULL
    	CONNECT BY	part_no	= PRIOR parent_part_no
    )
    SELECT	  indented_part_no
    ,	  parent_part_no
    ,	  quantity
    ,	  SUM (eval_number (cost_string)) / quantity	AS cost
    ,	  SUM (eval_number (cost_string))		AS total_cost
    FROM	  bottom_up
    GROUP BY  indented_part_no
    ,	  parent_part_no
    ,	  quantity
    ,	  r_num
    ORDER BY  r_num
    ;
    Output:
    INDENTED_  PARENT_
    PART_NO    PART_NO   QUANTITY       COST TOTAL_COST
    ---------- ------- ---------- ---------- ----------
    A                           2        300        600
      B        A                3        100        300
        C      B                4         25        100
          D    C                2         10         20
          E    C                1          5          5
    This is an example of a Yo-Yo Query , where the same section of the graph is traversed multiple times, in alternating directions. In this case, we start with a Top-Down query to get the universe of rows needed in this query, then do a Bottom-Up query to associate each leaf with all of its ancestors.

    When trying to understand complicated queries like this, run each sub-query by itself, and study the reult set of that sub_query.
    You'll see that the cost_string column of bottom_up is a string such as '(((10) * 2) * 4) * 3'. The user-defined function eval_number (see below) evaluates that string as a number, returning the NUMBER 240.

    This query assumes that a row has a non-NULL cost if and only if it has no children. If that's not the case, then the same basic idea will work, but it will be even messier.

    Here's how to create the eval_number function used above:
    CREATE OR REPLACE FUNCTION	eval_number
    (	in_txt	IN	VARCHAR2
    ,	err_val	IN	NUMBER	DEFAULT	NULL
    )
    RETURN	NUMBER
    IS
    --	eval_number attempts to evaluate in_txt as a NUMBER,
    --	returning that value.  If in_txt can not be interpreted,
    --	then err_val is returned.
    
    	result_txt	VARCHAR2 (100);
    	return_val	NUMBER;
    BEGIN
    	EXECUTE IMMEDIATE	'SELECT '
    			||	in_txt
    			||	' FROM dual'
    			INTO	return_val;
    --	dbms_output.put_line (result_val || ' = result_txt in eval_number');
    	RETURN	return_val;
    EXCEPTION
    	WHEN OTHERS
    	THEN
    --		dbms_output.put_line (SQLERRM || ' = error in eval_number');
    		RETURN	err_val;
    END	eval_number
    ;
    /
    SHOW ERRORS
  • 795486
    795486 Member Posts: 16
    Thank you for your help. I'm going to start looking at this now.
  • Rob van Wijk
    Rob van Wijk Member Posts: 5,856 Bronze Trophy
    And here is a solution without the need for PL/SQL, using the SQL model clause introduced in version 10:
    SQL> WITH t_data AS
      2  (
      3          SELECT 'A' AS part_no, NULL AS parent_part_no, 2 AS quantity, NULL AS cost FROM DUAL UNION ALL
      4          SELECT 'B' AS part_no, 'A' AS parent_part_no, 3 AS quantity, NULL AS cost FROM DUAL UNION ALL
      5          SELECT 'C' AS part_no, 'B' AS parent_part_no, 4 AS quantity, NULL AS cost FROM DUAL UNION ALL
      6          SELECT 'D' AS part_no, 'C' AS parent_part_no, 2 AS quantity, 10 AS cost FROM DUAL UNION ALL
      7          SELECT 'E' AS part_no, 'C' AS parent_part_no, 1 AS quantity, 5 AS cost FROM DUAL
      8  )
      9  , t_tree as
     10  ( select part_no
     11         , parent_part_no
     12         , connect_by_root part_no root_part_no
     13         , level lvl
     14         , quantity
     15         , cost
     16      from t_data
     17   connect by parent_part_no = prior part_no
     18     start with parent_part_no is null
     19  )
     20  select part_no        "Part No"
     21       , parent_part_no "Parent Part No"
     22       , quantity       "Quantity"
     23       , cost           "Cost"
     24       , total_cost     "Total Unit Cost"
     25    from t_tree
     26   model
     27         partition by (root_part_no)
     28         dimension by (parent_part_no, part_no)
     29         measures (lvl,quantity,cost,0 total_cost)
     30         ( total_cost[any,any] order by lvl desc,part_no
     31           = quantity[cv(),cv()]
     32             * ( nvl(cost[cv(),cv()],0)
     33               + nvl(sum(total_cost)[cv(part_no),any],0)
     34               )
     35         , cost[any,any] order by lvl desc, part_no
     36           = total_cost[cv(),cv()] / quantity[cv(),cv()]
     37         )
     38   order by root_part_no
     39       , parent_part_no nulls first
     40       , part_no
     41  /
    
    Part No    Parent Part No   Quantity       Cost Total Unit Cost
    ---------- -------------- ---------- ---------- ---------------
    A                                  2        300             600
    B          A                       3        100             300
    C          B                       4         25             100
    D          C                       2         10              20
    E          C                       1          5               5
    
    5 rows selected.
    Regards,
    Rob.
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited September 2010
    Very great model clause solution "Rob van Wijk" ;-)

    I consider model clause solution and recursive with clause solution.
    I did not find effective recursive with clause solution. :8}
    col parent_part_no for a20
    
    WITH t_data(part_no,parent_part_no,quantity,cost) AS(
    SELECT 'A',NULL,2,NULL FROM DUAL UNION ALL
    SELECT 'B','A' ,3,NULL FROM DUAL UNION ALL
    SELECT 'C','B' ,4,NULL FROM DUAL UNION ALL
    SELECT 'D','C' ,2,  10 FROM DUAL UNION ALL
    SELECT 'E','C' ,1,   5 FROM DUAL)
    select part_no,parent_part_no,quantity,cost,
    cost*quantity as "Total Unit Cost"
      from t_data
    start with parent_part_no is null
    connect by prior part_no = parent_part_no
    model
    dimension by(part_no,parent_part_no)
    measures(quantity,cost,
             Row_Number() over(order by Level) as LV)
    rules(cost[any,any] order by LV desc=
    case when cost[cv(),cv()] is null
         then sum(cost*quantity)[any,cv(part_no)]
         else cost[cv(),cv()] end);
    
    P  PARENT_PART_NO  QUANTITY  COST  Total Unit Cost
    -  --------------  --------  ----  ---------------
    A  null                   2   300              600
    B  A                      3   100              300
    C  B                      4    25              100
    D  C                      2    10               20
    E  C                      1     5                5
  • 795486
    795486 Member Posts: 16
    I appreciate the further posts. I'm working on the various options now and will reply when i have it working. Again, thanks for the help!
  • 795486
    795486 Member Posts: 16
    I have a question about the part_no and parent_part_no. When i run my queries, i'm returning 20k records and i should only have 111. I think its because the part_no is not unique (Part No AA56032-IBLK is listed twice but has two different parents). Should I be using the unique id's of the part_no and the parent_part_no in the connect_by?

    In my scenario, the part_no and parent_part_no are NOT the unique_ids. They are the actual part_no in the Bill of Material twice but under two different parent_part_no. For example,
    Part_no        Parent_part_no       Level       Total Unit Cost    Quantity
    20706123-101	20706123-1		1		                3
    20706123-107	20706123-101		2		                1
    AA56032-IBLK	20706123-107		3	0.591	                1
    M22759/11-22-9	20706123-107		3	0.15645564		8
    M23053/5-104-9	20706123-107		3	0.28275793		8
    M23053/5-107-9	20706123-107		3	0.02144708		5
    MS25036-102	20706123-107		3	0.368634	        3
    SMS001789	20706123-107		3		                3
    M23053/5-105-9	SMS001789		4	0.0209143	        2
    AA56032-IBLK	20706123-101		2	0.591	                1
    M23053/5-104-9	20706123-101		2	0.28275793		3
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,497 Red Diamond
    Hi,
    BigWaveDave wrote:
    I have a question about the part_no and parent_part_no. When i run my queries, i'm returning 20k records and i should only have 111. I think its because the part_no is not unique (Part No AA56032-IBLK is listed twice but has two different parents). Should I be using the unique id's of the part_no and the parent_part_no in the connect_by?

    In my scenario, the part_no and parent_part_no are NOT the unique_ids. They are the actual part_no in the Bill of Material twice but under two different parent_part_no. For example,
    Part_no        Parent_part_no       Level       Total Unit Cost    Quantity
    20706123-101	20706123-1		1		                3
    20706123-107	20706123-101		2		                1
    AA56032-IBLK	20706123-107		3	0.591	                1
    M22759/11-22-9	20706123-107		3	0.15645564		8
    M23053/5-104-9	20706123-107		3	0.28275793		8
    M23053/5-107-9	20706123-107		3	0.02144708		5
    MS25036-102	20706123-107		3	0.368634	        3
    SMS001789	20706123-107		3		                3
    M23053/5-105-9	SMS001789		4	0.0209143	        2
    AA56032-IBLK	20706123-101		2	0.591	                1
    M23053/5-104-9	20706123-101		2	0.28275793		3
    You know what to do now: post some sample data in a form that people can use to re-create the problem and test their solutions.
    A WITH clause, like Centinul posted, is fine. So are CREATE TABLE and INSERT statements.
    Also post the results you want to get from that data.
    Point out where the queries suggested so far are getting the wriong results.
  • Rob van Wijk
    Rob van Wijk Member Posts: 5,856 Bronze Trophy
    BigWaveDave wrote:
    I have a question about the part_no and parent_part_no. When i run my queries, i'm returning 20k records and i should only have 111. I think its because the part_no is not unique (Part No AA56032-IBLK is listed twice but has two different parents). Should I be using the unique id's of the part_no and the parent_part_no in the connect_by?
    Yes, you should.
  • 795486
    795486 Member Posts: 16
    I wanted to thank everyone for their help. I successfully implemented both methods recommended in the responses.

    Appreciate the help!
  • jampala - oracle
    jampala - oracle Member Posts: 6
    edited March 2013
    Hi Rob,
    This is an excellent piece of 'Model' clause usage... I have a similar requirement... I want to 'Aggregate' only Leaf nodes of the BOM Tree. This is somethig we need for 'Material Aggregation'. All the Leaf Nodes are Raw Materials and we need to aggregate each of them in a given BOM.

    Thanks
    JJ

    WITH t_data AS
    (
    SELECT 'A' AS part_no, NULL AS parent_part_no, 1 AS quantity FROM DUAL UNION ALL
    SELECT 'B' AS part_no, 'A' AS parent_part_no, 3 AS quantity FROM DUAL UNION ALL
    SELECT 'C' AS part_no, 'B' AS parent_part_no, 4 AS quantity FROM DUAL UNION ALL
    SELECT 'F' AS part_no, 'B' AS parent_part_no, 2 AS quantity FROM DUAL UNION ALL
    SELECT 'D' AS part_no, 'F' AS parent_part_no, 3 AS quantity FROM DUAL UNION ALL
    SELECT 'D' AS part_no, 'C' AS parent_part_no, 2 AS quantity FROM DUAL UNION ALL
    SELECT 'E' AS part_no, 'C' AS parent_part_no, 1 AS quantity FROM DUAL
    )



    Table Structure:

    Part No Parent Part No Quantity_

    A | | 1
    B | A | 3
    C | B | 4
    F | B | 2
    D | C | 2
    E | C | 1
    D | F | 3

    Expected Result*

    Root Parent Part No Total Quantity*

    A | D | 42
    A | E | 12

    Total Quantity of Leaf Node 'D': 42 (2 X 4 X 3 X 1) + (3 X 2 X 3 X 1)
    Total Quantity of Leaf Node 'E': 12 (1 X 4 X 3 X 2)

    Edited by: JJ on Mar 30, 2013 5:48 PM

    Edited by: JJ on Mar 30, 2013 6:26 PM

    Edited by: JJ on Mar 30, 2013 6:38 PM

    Edited by: JJ on Mar 30, 2013 6:39 PM

    Edited by: JJ on Mar 30, 2013 6:39 PM

    Edited by: JJ on Mar 30, 2013 6:40 PM
  • jampala - oracle
    jampala - oracle Member Posts: 6
    Hi Aketi,
    I have a similar request to Aggregate only Leaf Nodes of the BOM Tree... I could achieve partially by customizing your solution by traversing bottom-up... Child to Root Parent. But this approcah fails if we have duplicate Leaf Nodes at different Levels of BOM

    WITH t_data AS
    (
    SELECT 'A' AS part_no, NULL AS parent_part_no, 2 AS quantity FROM DUAL UNION ALL
    SELECT 'B' AS part_no, 'A' AS parent_part_no, 3 AS quantity FROM DUAL UNION ALL
    SELECT 'C' AS part_no, 'B' AS parent_part_no, 4 AS quantity FROM DUAL UNION ALL
    SELECT 'F' AS part_no, 'B' AS parent_part_no, 2 AS quantity FROM DUAL UNION ALL
    SELECT 'D' AS part_no, 'F' AS parent_part_no, 3 AS quantity FROM DUAL UNION ALL
    SELECT 'D' AS part_no, 'C' AS parent_part_no, 2 AS quantity FROM DUAL UNION ALL
    SELECT 'E' AS part_no, 'C' AS parent_part_no, 1 AS quantity FROM DUAL
    )

    This Works_

    select part_no,parent_part_no,quantity,cost,
    cost*quantity as "Total Unit Cost"
    from t_data2
    start with part_no in ('E')
    connect by prior parent_part_no = part_no
    model UNIQUE SINGLE REFERENCE
    dimension by(part_no,parent_part_no)
    measures(quantity,cost,
    Row_Number() over(order by Level) as LV)
    rules(cost[any,any] order by LV asc=
    case when cost[cv(),cv()] is null
    then sum(cost*quantity)[any,cv(part_no)]
    else cost[cv(),cv()] end);

    Result

    PART NO PARENT PART NO QUANTITY COST Total Unit Cost_

    E | C | 1 | 1 | 1
    C | B | 4 | 1 | 4
    B | A | 3 | 4 | 12

    This doesn't work_

    select part_no,parent_part_no,quantity,cost,
    cost*quantity as "Total Unit Cost"
    from t_data2
    start with part_no in ('D')
    connect by prior parent_part_no = part_no
    model UNIQUE SINGLE REFERENCE
    dimension by(part_no,parent_part_no)
    measures(quantity,cost,
    Row_Number() over(order by Level) as LV)
    rules(cost[any,any] order by LV asc=
    case when cost[cv(),cv()] is null
    then sum(cost*quantity)[any,cv(part_no)]
    else cost[cv(),cv()] end);

    Error Message:_

    ORA-32638: Non unique addressing in MODEL dimensions
    32638. 00000 - "Non unique addressing in MODEL dimensions"
    *Cause: The address space defined for the MODEL (partition by and dimension by
    expressions) do not uniquely identify each cell.
    *Action: Rewrite the MODEL clause. Using UNIQUE SINGLE REFERENCE
    option might help.
This discussion has been closed.