Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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,918 views