Maximo Open Forum

 View Only

 SQL Query for Maximo KPI

  • Life Science/Calibration
Stefano Giorgi's profile image
Stefano Giorgi posted 06-21-2022 04:34
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's profile image
Stefano Giorgi
I have found an answer which works for anyone else who may need it.

You should be able to use a join if you wrap the statement, treating it like a temp table…  To do this, we 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. Let’s have you try to 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

pavan Kumar's profile image
Maximo Certified pavan Kumar
Hi Stefano,

You can try the below query, 

select count(workorder.wonum) from workorder
where istask = 0 and workorder.pmnum is not null and workorder.gmpclassification in ('gmp', 'gxp')
and workorder.status in ('COMP','CLOSE') and workorder.statusdate > getdate()-7