Query Help: Totaling parent / child items

Question

[Ed. Note: Related to this SO question; didn't edit question because the problem has fundamentally shifted and question/answers from before are still valid]

Looking for advice of how best to create a view. The current query from my previous problem now works, but doesn't account for the idea of parent/child items.

Relevant Data structure

+---------+      +---------+
|WORKORDER|      |WPLABOR  |
|---------|      |---------|
|WONUM    |+---->|WONUM    |
|PARENT   |  +   |LABORHRS |
|ISTASK   |  |   |RATE     |
|...      |  |   +---------+
|         |  |
+---------+  |   +---------+
             |   |WPITEM   |
             |   |---------|
             +-->|WONUM    |
             |   |ITEMQTY  |
             |   |UNITCOST |
             |   +---------+
             |
             |   +----------------+
             |   |LONGDESCRIPTION |
             |   |----------------|
             +-->|LDKEY           |
                 |LDTEXT          |
                 +----------------+

Goal

In layman's terms, I am having someone enter a work order number, and pulling a report that has the work order's information, plus a summary of labor and materials cost for all of the child workorders (also called task workorders)

Therefore, I need a view that returns:

• The standard information about that workorder (ID'd by WONUM) -- description, location, etc.
• Total Labor Hours (SUM of LABORHRS) for each of its child task workorders
• Total Labor cost (sum of LABORHRS*RATE) from WPLABOR For each of its child task workorders
• Total Item Cost (SUM of ITEMQTY*UNITCOST) from WPITEM For each of its child task workorders

A Child Task Workorder is a workorder where parent = WONUM of first workorder and ISTASK=1

Use:

I would like to be able to call this in the following way:

SELECT * from [ViewName] where wonum = '123abc';

I know this involves a combination of subqueries and joins, I'm just not sure what the best way to structure the relationships, etc. is.

Current Query

SELECT WORKORDER.WONUM      ,
       WORKORDER.ACTLABHRS  ,
       WORKORDER.LOCATION   ,
       WORKORDER.STATUS     ,
       WORKORDER.WO7        , -- Requester
       WORKORDER.WO8        , -- Extension
       WORKORDER.WO9        , -- Location
       WORKORDER.LEADCRAFT  ,
       WORKORDER.WO11       , -- Extension
       WORKORDER.GLACCOUNT  ,
       WORKORDER.WO10       , -- Contact
       WORKORDER.DESCRIPTION, -- Short description
       WORKORDER.WO6        , -- Plant rearrangement (YORN / boolean value)
       WORKORDER.ISTASK     ,
       WORKORDER.PARENT     ,
       LABOR.TOTALLABORHRS  ,
       LABOR.LABORCOST      ,
       ITEM.ITEMCOST        ,
       DESCRIPTION.LDTEXT
FROM   MAXIMO.WORKORDER
       LEFT JOIN
              ( SELECT  WPLABOR.WONUM                                    ,
                       SUM(WPLABOR.LABORHRS * WPLABOR.RATE) AS LABORCOST ,
                       SUM(WPLABOR.LABORHRS)                AS TOTALLABORHRS
              FROM     MAXIMO.WPLABOR
              GROUP BY WONUM
              )
              LABOR
       ON     WORKORDER.WONUM = LABOR.WONUM
       LEFT JOIN
              ( SELECT  WPITEM.WONUM ,
                       SUM(WPITEM.ITEMQTY * WPITEM.UNITCOST) AS ITEMCOST
              FROM     MAXIMO.WPITEM
              GROUP BY WONUM
              )
              ITEM
       ON     WORKORDER.WONUM = ITEM.WONUM
       LEFT JOIN
              ( SELECT LONGDESCRIPTION.LDKEY,
                      LONGDESCRIPTION.LDTEXT
              FROM    MAXIMO.LONGDESCRIPTION
              WHERE   LONGDESCRIPTION.LDOWNERTABLE='WORKORDER'
              AND     LONGDESCRIPTION.LDOWNERCOL  = 'DESCRIPTION'
              )
              DESCRIPTION
       ON     WORKORDER.WONUM = CAST(DESCRIPTION.LDKEY AS VARCHAR(22)) HERE WORKORDER.ISTASK = 1;

Issues with Current Query

Because it doesn't take child task workorders into account, it doesn't return any labor or item records (none are directly associated with the parrent), and so the labor and item totals are always empty.

Answer 1

I've come up with a query that resolves the situation and is a little faster than "connect by prior "method (3 seconds compared to ~90 seconds).

To make it a little easier, I separated the standard Work Order information into a separate view (I'd like to re-use it anyway).

The query I ended up with (below) seems to do the trick:

CREATE OR replace VIEW r_wo_costsummariesbyparent 
AS 
  SELECT workorder.parent, 
         Nvl(SUM(wosummary.totallaborhoursforwo), 0) AS totallaborhoursforparent, 
         Nvl(SUM(wosummary.totallaborcostforwo), 0)  AS totallaborcostforparent, 
         Nvl(SUM(wosummary.totalitemcostforwo), 0)   AS totalitemcostforparent 
  FROM   maximo.workorder 
         inner join (SELECT workorder.wonum, 
                            Nvl(SUM(wplabor.laborhrs), 0) AS totallaborhoursforwo, 
                            Nvl(SUM(wplabor.laborhrs * wplabor.rate), 0) AS totallaborcostforwo, 
                            Nvl(SUM(wpitem.itemqty * wpitem.unitcost), 0) AS totalitemcostforwo 
                     FROM   maximo.workorder 
                            inner join maximo.wplabor 
                              ON ( workorder.wonum = wplabor.wonum ) 
                            inner join maximo.wpitem 
                              ON ( workorder.wonum = wpitem.wonum ) 
                     WHERE  workorder.istask = 1 
                     GROUP  BY workorder.wonum) wosummary 
           ON workorder.wonum = wosummary.wonum 
  GROUP  BY workorder.parent; 

I use it in the following way:

Select * from r_wo_costsummariesbyparent where parent = '123abc';