Maximo Open Forum

 View Only

 Where Query for related records

  • Work Management
Elliott McRobert's profile image
Elliott McRobert posted 07-28-2022 19:01
HI Team,

My knowledge of SQL is fairly basic so really struggling. I'm trying to get a list of work orders that are related to a certain work order. The query below works only for this instance due to some weird firewall rule that means I can't use the exists function.

Anyone got an idea how i can do it differently. Any help much appreciated.



(exists ( select * from relatedrecord where workorder.wonum=convert(varchar,relatedrecord.relatedreckey) and recordkey = '647670') and siteid = 'cml') and ((persongroup like 'CTR%'))

Christopher Winston's profile image
Maximo Certified Christopher Winston
Work Order Tracking Advanced Search has the Related WO field




That generates SQL: 

((woclass = 'WORKORDER' or woclass = 'ACTIVITY') and historyflag = 0 and siteid = 'BEDFORD' and istask = 0) and (exists (select 1 from dbo.relatedrecord where ((emxrelatedwonum like '%12345678%')) and (siteid = workorder.siteid and recordkey=workorder.wonum and class =workorder.woclass and relatedrecclass in (select value from synonymdomain where domainid ='WOCLASS' and maxvalue in ('CHANGE', 'RELEASE','ACTIVITY', 'WORKORDER')))))

Person Group does not come on the advanced search out of the box, so you will need to add it, and then your users can just use the screen and not have to deal wtih the SQL
Elliott McRobert's profile image
Elliott McRobert
HI, thanks for this. My advance search doesnt have that! WE dont have access to application designer to change it as its a client system. I should be able to work with the query you sent below. thanks so much
Elliott McRobert's profile image
Elliott McRobert
also cant use EXIST clause so didnt work
Christopher Winston's profile image
Maximo Certified Christopher Winston
the emxrelatedwonum attribute is more than likely the issue; it is added to our demo in the related records object in db config, which I am guessing you don't have access to, so let's try something else. 

for an originating record and those related to it:

((woclass = 'WORKORDER' or woclass = 'ACTIVITY') and historyflag = 0 and origrecordid like '%12345678%' and siteid = 'BEDFORD' and istask = 0)

this gets rid of the 'exists'.

Just curious - what database? 
Elliott McRobert's profile image
Elliott McRobert
that didnt work. I have the original record field in my advance search and tried it previously. A ticket has been raised to fix the exist issue so maybe I just have to wait. thanks for your help
Nikolaus Despain's profile image
Maximo Certified Nikolaus Despain
Why don't you try an embedded query...  If I understand what you are trying to do (find related work orders from the work order screen, where "647670" is the original record [sometimes shown on the screen])...

Enter this Query in the Where Clause (this embedded query will check wonum's against relatedreckey value in relatedrecord table) -


wonum in (select relatedreckey from relatedrecord where workorder.origrecordid = '647670') and siteid = 'cml' and persongroup like 'CTR%'