0

I have a WORKORDER table that has parent and child WOs in it:

with workorder as (
select 'WO37342' as wonum,      null as parent, 297.36 as actlabcost, 200 as actmatcost, 0 as actservcost, 0 as acttoolcost from dual
union all
select 'WO37427' as wonum, 'WO37342' as parent,  99.12 as actlabcost,   0 as actmatcost, 0 as actservcost, 0 as acttoolcost from dual
union all
select 'WO37429' as wonum, 'WO37342' as parent,  99.12 as actlabcost, 100 as actmatcost, 0 as actservcost, 0 as acttoolcost from dual
)
select
    * 
from
    workorder

 WONUM   PARENT  ACTLABCOST ACTMATCOST ACTSERVCOST ACTTOOLCOST
 ------- ------- ---------- ---------- ----------- -----------
 WO37342             297.36        200           0           0
 WO37427 WO37342      99.12          0           0           0
 WO37429 WO37342      99.12        100           0           0

I want to select the parent rows and include the cost of the children in the parents:

 WONUM    ACTLABCOST ACTMATCOST ACTSERVCOST ACTTOOLCOST
 ------- ----------- ---------- ----------- -----------
 WO37342       495.6        300           0           0

Is there a concise way of doing this with Oracle 18c SQL?

1 Answer 1

1

That ability was available in Oracle 7; maybe earlier. ( doc )

  1. Use a hierarchical query (in a CTE/sub-query) to find the children and grandchildren.
  2. SUM() the values when you GROUP BY the parent wonum.
with workorder as (
    select 'WO37342' as wonum,      null as parent, 297.36 as actlabcost, 200 as actmatcost, 0 as actservcost, 0 as acttoolcost from dual
    union all
    select 'WO37427' as wonum, 'WO37342' as parent,  99.12 as actlabcost,   0 as actmatcost, 0 as actservcost, 0 as acttoolcost from dual
    union all
    select 'WO37429' as wonum, 'WO37342' as parent,  99.12 as actlabcost, 100 as actmatcost, 0 as actservcost, 0 as acttoolcost from dual
), hier as (
    select connect_by_root( wonum ) parent_wonum
        ,level lv
        ,a.*
    from workorder a
    start with a.parent is null
    connect by a.parent = prior(a.wonum)
)
select parent_wonum
    ,listagg(wonum,',') within group (order by lv, wonum) list_wo
    ,sum( actlabcost ) actlabcost
    ,sum( actmatcost ) actmatcost
    ,sum( actservcost ) actservcost
    ,sum( acttoolcost ) acttoolcost
from hier
group by parent_wonum    

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.