Thank you. That did the trick. As this was for use in KPI Manager I was also given another solution that allowed me to use my original sql query which maybe of interest to others.
You should be able to use a join if you wrap the statement, treating it like a temp table… To do this, you need to select a single entry from "some table" and thankfully, we have dummy_table that Maximo uses to do temporary storage of blob/clob. This table will only ever have a single row in it, we have been using it for many years. Put this into the "select" field of the KPI only (leave the where clause empty).
select
(
select count(workorder.wonum) from workorder
left join wostatus on workorder.wonum = wostatus.wonum
where
istask = 0
and workorder.pmnum is not null
and workorder.gmpclassification in ('gmp', 'gxp')
and (workorder.status in ('comp','close')
and (wostatus.status = workorder.status)
and wostatus.changedate > getdate()-7)
) from dummy_table
------------------------------
Stefano Giorgi
------------------------------
Original Message:
Sent: Tue June 21, 2022 12:34 PM
From: Tim Ferrill
Subject: SQL query for Maximo KPI
Stefano,
Something like this should work:
select count(workorder.wonum) from workorderwhere istask=0 and pmnum is not null and gmpclassification in ('gmp','gxp') and exists (select 1 from wostatus where workorder.wonum = wostatus.wonum and status in ('COMP','CLOSE') and changedate>getdate()-7)
------------------------------
Tim Ferrill
Solutions Consultant
Intelligent Technology Solutions
tferrill@webuildits.com
www.webuildits.com
@tferrill/@webuildits
------------------------------