2

I am trying to create a where clause to return all work orders between set dates where the there are no ACTUALS recorded (no labtrans). I have an existing WC which i am using as a template for this one but i am stuck with the "where" element.

(woclass = 'WORKORDER' or woclass = 'ACTIVITY') and
istask = 0 and
worktype = 'PPM' and *This could be removed*
targcompdate >= { ts '2020-05-01 00:00:00.000' } and
targcompdate >= { ts '2020-05-05 00:00:00.000' } and
not exists?? there are no actuals are on the work order?

Many Thanks,

G

Garth
  • 53
  • 9
  • As further notes on the query: ACTIVITY class work orders are tasks, so by filtering out tasks with the "istaks = 0" clause (which does appear to be correct for you), you make the "or woclass = 'ACTIVITY'" clause not do anything. Also, you have where targcompdate is greater than May 1st *and* greater than May 5th? That first check isn't needed then. Should that second check maybe be *less than* May 5th instead? – Dex May 06 '20 at 13:14
  • Hi Dex, good spot on the date error... I have found a solution.. Thanks – Garth May 07 '20 at 21:03

2 Answers2

3

The easy way, trusting that the rest of Maximo has done its job like it normally does, would be something like the following. (I'm going from memory. You can double-check the column names on the WORKORDER object / table in Database Configuration or in your database browser tool, like SQL Developer.)

(woclass = 'WORKORDER' or woclass = 'ACTIVITY') and
istask = 0 and
worktype = 'PPM' and *This could be removed*
targcompdate >= { ts '2020-05-01 00:00:00.000' } and
targcompdate >= { ts '2020-05-05 00:00:00.000' } and
actlabcost = 0 and
actmatcost = 0 and
actservcost = 0 and
acttoolcost = 0
Preacher
  • 2,127
  • 1
  • 11
  • 25
0

This works for me:

(woclass = 'WORKORDER' or woclass = 'ACTIVITY') and 
(targcompdate between '2020-05-01' and '2020-05-05' and historyflag = 0 and istask = 0) 
and not exists(select 1 from labtrans where refwo=workorder.wonum and siteid=workorder.siteid)
laserlite
  • 1
  • 2