Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

Rollup Hierarchy SQL

795486Sep 2 2010 — edited Mar 30 2013
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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 27 2013
Added on Sep 2 2010
14 comments
10,655 views