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
Original Message:
Sent: Tue June 21, 2022 04:31 AM
From: Stefano Giorgi
Subject: SQL query for Maximo KPI
It's been a while since I have created a sql query and am after a bit of help.
I need a KPI that shows me the number of certain WOs comp/closed in the last days.
The following works in my SQL tool, but as you can't have joins in KPIs I need something different.
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)
I tried the following
select count(workorder.wonum) from workorder
where
workorder.istask = 0
and workorder.pmnum is not null
and workorder.gmpclassification in ('gmp', 'gxp')
and (workorder.status in ('comp','close')
and workorder.status in (select status from wostatus where wostatus.status = workorder.status and (wostatus.changedate > getdate()-7))
but get the following error
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near ')'.
------------------------------
Stefano Giorgi
------------------------------
#AssetandFacilitiesManagement
#Maximo