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:
- 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.
- 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:
The blue boxes show examples of parents with children.
--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?