IBM's Maximo Asset Management platform has a WORKORDER table (265 columns).

Details:

The WORKORDER table contains two different kinds of rows:

• WO records (istask = 0)
• Task records (istask = 1).

The table has columns that store the actual costs (populated for both non-task WOs and for tasks):

• actlabcost (actual labor cost)
• actmatcost (actual material cost)
• actservcost (actual services cost)
• acttoolcost (actual tool costs)

---

View:

I've written a query/view that selects non-task WOs.

For those non-task workorders, the view rolls up the costs from the related tasks and summarizes them in these columns:

• actlabcost_incltask
• actmatcost_incltask
• actservcost_incltask
• acttoolcost_incltask
• acttotalcost_incltask

I plan to use the view for multiple reports. So I've included all 265 columns in the view via select * (although, Oracle will convert the select * to actual column names when the view is created).

--WO_INCL_TASK_ACT_VW (non-task WOs, including task actuals) select t.task_actlabcost, t.task_actmatcost, t.task_actservcost, t.task_acttoolcost, t.task_acttotalcost, nt.actlabcost + t.task_actlabcost as actlabcost_incltask, nt.actmatcost + t.task_actmatcost as actmatcost_incltask, nt.actservcost + t.task_actservcost as actservcost_incltask, nt.acttoolcost + t.task_acttoolcost as acttoolcost_incltask, t.task_acttotalcost + nt.actlabcost + nt.actmatcost + nt.actservcost + nt.acttoolcost as acttotalcost_incltask, nt.* from workorder nt --non-task WOs left join ( select parent, sum(actlabcost) as task_actlabcost, sum(actmatcost) as task_actmatcost, sum(actservcost) as task_actservcost, sum(acttoolcost) as task_acttoolcost, sum(actlabcost) + sum(actmatcost) + sum(actservcost) + sum(acttoolcost) as task_acttotalcost from workorder group by parent, istask having istask = 1 ) t --tasks on nt.wonum = t.parent where nt.istask = 0 

Question:

The view works just fine. However, it's fairly lengthy for what it does.

Can it be improved?