Maximo

Expand all | Collapse all

Querying Owners in an Ownergroup

  • 1.  Querying Owners in an Ownergroup

    Posted Wed January 08, 2020 08:58 AM
    For work orders who have an assigned owner, is there a way to query all work orders who have owners from a particular persongroup?

    For example
    Work Order 1 has owner 'AAA'
    Work Order 2 has owner 'BBB'
    'AAA' and 'BBB' are part of persongroup: 'persongroup1'

    Query all people from 'persongroup1' to return work order 1 and 2

    ------------------------------
    Alex Wong
    ------------------------------


  • 2.  RE: Querying Owners in an Ownergroup

    Posted Wed January 08, 2020 09:27 AM
    Alex,
    I'd use an exists statement against the persongroupteam table. That should return all work orders where the workorder.owner is present in the Person Group, and should be a fairly effective SQL query. You could use IN as the clause too, and with the tables we're talking about here, it might be just as quick - but in general EXISTS will be quicker for very large tables.

    SELECT * FROM workorder WHERE EXISTS (SELECT 1 FROM persongroupteam pgt WHERE pgt.persongroup='TG1' AND workorder.owner=pgt.respparty);​


    ------------------------------
    Henrik Christiansen
    ------------------------------