Skip to Main Content
Forums

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

fac586 
helpme_helpyou wrote:
I created select list with the data I have. But, i get two empty rows (selection) before the actual names i want to get shown. Can anyone help me with it, please?

What are the settings for the Display Extra Values and Display Null Values properties on the select list item? (Read the online help for these properties while you're there).

3474044

They were YES, but I just changed them to NO. Even tho, i still have one empty selection. (one of them removed already and thanks for helping)

Capture.PNGCapture1.PNG

fac586 
helpme_helpyou wrote:
They were YES, but I just changed them to NO. Even tho, i still have one empty selection. (one of them removed already and thanks for helping)

So what's in the data? Run the LOV query in SQL Workshop.

3474044

how can i do that? i have never done that and i am new on APEX.

thanks for your help!

fac586 
helpme_helpyou wrote:
how can i do that? i have never done that and i am new on APEX.
3474044

Thanks for the steps! I got four rows which includes one extra (empty row). How can i avoid that one? Please, see the following screenshot for the results.

Capture.PNG

3474044

I found the problem. I added additional column without putting Location type into it. I think it causes it to show one empty selection.

Thanks for helping

fac586 
helpme_helpyou wrote:
Thanks for the steps! I got four rows which includes one extra (empty row). How can i avoid that one?

I have no idea because I have no knowledge of the data model. However, the LOV query indicates that the data model is not properly normalized, providing further evidence that it is probably wrong.

3474044
Answer

i found the problem. It should be there (the one of the empty selection), because the user also may choose nothing for that which required empty selection. can you please help me with another question which i just shared 5-10 mins ago? thanks again for helping!

Marked as Answer by 3474044 · Sep 27 2020
1 - 9
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,922 views