Maximo Open Forum

 View Only

  • 1.  Query to find any WORKORDER table record that came from a PM

    Posted 06-03-2021 09:54
    Does anyone have a query that can find ALL the records in the WORKORDER table that came from a PM?  I mean this could be a "standalone" PM, all the PM's from a PM hierarchy, a child Work Order that came about because of a Route on the PM, a Task from a Job Plan on a PM, etc.

    I have a query that I believe works, but in the back of my mind I wonder if it works for "what we do today" but not all Maximo can do (for example, I have PM's with Routes, but none of the Route Stops have Job Plans).  More importantly, the query I'm using now is terribly inefficient and is causing performance problems.

    ...actually, as I'm writing this, the idea came to mind just to query WOANCESTOR --

    wonum in (select wonum from woancestor where ancestor in (select wonum from workorder where pmnum is not null))



    That's a whole lot more succinct than what I'm using now.  Does anyone have a better way, or even a way of improving the performance of this one?  (e.g., using EXISTS instead of IN)
    #Administration
    #EverythingMaximo

    ------------------------------
    Travis Herron
    Pensacola Christian College
    ------------------------------


  • 2.  RE: Query to find any WORKORDER table record that came from a PM

    Posted 06-03-2021 10:28
    Travis,

    This sounds like 2 queries, so let's see if we can make them work together. 

    Work orders from PM's have the pmnum filled in, so we can start there.

    select wonum from workorder where pmnum is not null and siteid = 'BEDFORD'

    WOANCESTOR will track the hierarchy, so we can look there as well

    SELECT WONUM FROM WOANCESTOR WHERE ANCESTOR IN (select wonum from workorder where pmnum is not null and siteid = 'BEDFORD')


    So, together:

    select wonum from workorder where pmnum is not null and siteid = 'BEDFORD'
    UNION
    SELECT WONUM FROM WOANCESTOR WHERE ANCESTOR IN (select wonum from workorder where pmnum is not null and siteid = 'BEDFORD');

    there is probably a more elegant way that someone will provide, but this should get you started. 





    ------------------------------
    Christopher Winston
    Projetech Inc.
    ------------------------------

    Original Message


  • 3.  RE: Query to find any WORKORDER table record that came from a PM
    Best Answer

    Posted 06-04-2021 08:59
    Yeah WOANCESTOR is the way I would approach this if you need to see the entire hierarchy for PM generated WO. I would definitely utilize an exists over IN for a couple of reasons, but the most important is to ensure that the tables are joined properly if you have a multisite system. 

    It's not clear if you want this as a saved query in Maximo (where you can only provide a where clause) or something you want for a report (internal or external to Maximo). And what all you want from the child WO/tasks. Those would adjust how I would write it so I'm going to provide a few different approaches.

    External query where you just need the WONUM/SITEID of each record.

    SELECT woancestor.wonum,woancestor.siteid
    FROM workorder
    INNER JOIN woancestor ON woancestor.ancestor=workorder.wonum and woancestor.siteid=workorder.siteid
    WHERE workorder.pmnum is not null

    External query where you need any field from WO. Supports both the main PM WO as well as the child WO/tasks

    SELECT wo.wonum, wo.siteid, wo.description, pmwo.wonum as pmwo, pmwo.pmnum, pmwo.description as pmwodescription
    FROM workorder pmwo
    INNER JOIN woancestor ON woancestor.ancestor=pmwo.wonum and woancestor.siteid=pmwo.siteid
    INNER JOIN workorder wo ON woancestor.wonum=wo.wonum and woancestor.siteid=wo.siteid
    WHERE pmwo.pmnum is not null

    Saved query in Maximo

    exists(SELECT 1 FROM woancestor INNER JOIN workorder z ON woancestor.ancestor=z.wonum and woancestor.siteid=z.siteid and z.pmnum is not null WHERE woancestor.wonum=workorder.wonum and woancestor.siteid=workorder.siteid)

    ------------------------------
    Steven Shull
    Projetech Inc.
    ------------------------------

    Original Message


Related Content