sql – Select workorders (via parameters) and their children

I’ve mocked-up a query that will eventually be used in a BIRT report (in IBM’s Maximo Asset Management platform).


The query/report will retrieve records as follows:

  1. Retrieve workorders based on the parameters that were selected by the user.
    • All of the parameters are optional.
    • For testing purposes, I’m using bind variables as parameters (Toad).
    • It doesn’t matter if the workorders are parents, children, or neither.
  2. Of the workorders that were retrieved, also select any children of those workorders.
    • There are only two levels to the parent/child hierarchy: parents and children (no grandchildren, etc.)

The output would look like this:

enter image description here

The blue boxes show examples of parents with children.


The query:

--short form definitions:
--wo   = workorder
--act  = actual (actual cost, actual date, etc.)
--pmtr = parameters

with 
wo_pmtr as (  --WOs filtered by parameters
select
    wonum,
    parent,
    hierarchypath,
    classstructureid,
    division,
    worktype,
    status, 
    trunc(actstart)  as actstart,
    trunc(actfinish) as actfinish,
    actlabcost,
    actmatcost,
    actservcost,
    acttoolcost,
    acttotalcost   
from
    workorder wo
where   
    --using bind variables as parameters in Toad
        (:wonum            is null     or wonum = :wonum)
    and (:division         is null     or division          = :division)
    and (:worktype         is null     or worktype          = :worktype)
    and (:status           is null     or status            = :status)
    and (:actstart         is null     or trunc(actstart)  >= :actstart)
    and (:actfinish        is null     or trunc(actfinish) <= :actfinish)

    --select where classification matches the parameter 
    --and select any child classifications too
    --(the classification hierarchy is not to be confused with the workorder hierarchy)
    and (:classstructureid is null     or (exists (select 1 from classancestor where ((ancestor = :classstructureid)) and (classstructureid=wo.classstructureid))))
)

select
    wo_pmtr.wonum,
    wo_pmtr.parent,
    wo_pmtr.hierarchypath,
    wo_pmtr.classstructureid,
    wo_pmtr.division,
    wo_pmtr.worktype,
    wo_pmtr.status, 
    wo_pmtr.actstart,
    wo_pmtr.actfinish,
    wo_pmtr.actlabcost,
    wo_pmtr.actmatcost,
    wo_pmtr.actservcost,
    wo_pmtr.acttoolcost,
    wo_pmtr.acttotalcost,
    coalesce(wo_pmtr.parent, wo_pmtr.wonum) parent_coalesced
from
    wo_pmtr      --WOs filtered by parameters
union            --union will remove duplicates (unlike union all)
select           --select children of the filtered WOs
    wo.wonum,
    wo.parent,
    wo.hierarchypath,
    wo.classstructureid,
    wo.division,
    wo.worktype,
    wo.status, 
    trunc(wo.actstart)  as actstart,
    trunc(wo.actfinish) as actfinish,
    wo.actlabcost,
    wo.actmatcost,
    wo.actservcost,
    wo.acttoolcost,
    wo.acttotalcost,
    coalesce(wo.parent, wo.wonum) parent_coalesced
from
    workorder wo  --select from the base workorder table *without* filtering by the parameters
left join
    wo_pmtr
    on wo.parent = wo_pmtr.wonum
where
    wo.parent        is not null   --where WO is a child
    and wo_pmtr.wonum is not null  --where WO is a child of the filtered WOs 
                                   --caution: some of those workorders might have already been selected via the parameters in the first query
                                   --we need to eliminate duplicates if they exist (via the union)
order by
    parent_coalesced, 
    hierarchypath

I’m new to reports and SQL queries like this. How can the query be improved?

  • For example: Is it a good practice to use UNION to eliminate duplicates?