Maximo Open Forum

Hi, is it possible to write an SQL statement in the Where Clause function, that returns only work orders holding only 1 assignment, not multiple?

Thankyou

Rob

Assuming you want only where the assignment is tied to the main work order (not at a task level) you can do something like below:

historyflag=0 and istask=0 and 1=(select count(1) from assignment where assignment.wonum=workorder.wonum and assignment.siteid=workorder.siteid)

Thanks Steven,

Is there a way to construct this further? If we wanted to see only workorders with 1 assignment, and that assignment held a specific craft?

Thanks

Rob

Since you want 1 assignment on the record and that 1 assignment must be for a specific craft, you'd need to do it like this:

historyflag=0 and istask=0 and 1=(select count(1) from assignment where assignment.wonum=workorder.wonum and assignment.siteid=workorder.siteid) and exists(select 1 from assignment where assignment.wonum=workorder.wonum and assignment.siteid=workorder.siteid and assignment.craft='YOURCRAFT')

Replacing 'YOURCRAFT' with the actual craft such as 'MECH'