Maximo Open Forum

I am trying to set up a portlet that would show SRs that have a related workorder with a particular workgroup on the related workorder.

I know I want to use this as the base of the where clause---how do I add in the criteria for a particular persongroup on the workorder?

((status = 'QUEUED' ) and (exists (select rel.recordkey from relatedrecord rel where rel.recordkey = ticketid and rel.relatedrecclass = 'WORKORDER'))) 

Hey Julia,

Try this and let me know if it gives you what you're expecting:

((status = 'QUEUED') and exists   (select 1    from relatedrecord rel    join workorder wo on rel.relatedreckey = wo.wonum    where rel.recordkey = ticketid      and rel.relatedrecclass = 'WORKORDER'      and wo.persongroup = 'group_name_here'))

Cheers,

I am trying to set up a portlet that would show SRs that have a related workorder with a particular workgroup on the related workorder.

I know I want to use this as the base of the where clause---how do I add in the criteria for a particular persongroup on the workorder?

((status = 'QUEUED' ) and (exists (select rel.recordkey from relatedrecord rel where rel.recordkey = ticketid and rel.relatedrecclass = 'WORKORDER'))) 

Hi---thank you, Matt.  I am trying the following but am getting the error:  BMXAA6713E - The MBO fetch operation failed in the mboset with the SQL error code 207. The record could not be retrieved from the database. See the log file for more details about the error.

I can run the where clause in SSMS and get results

(status <> 'RESOLVED')
and 
(exists 
  (select 1 
   from relatedrecord rel 
   join workorder wo on wo.wonum = rel.relatedreckey 
   where rel.recordkey = ticketid 
   and rel.relatedrecclass = 'WORKORDER'
   and wo.persongroup = 'ITPM' 
   )
)
and 
((commoditygroup = 'WASTEWATER' or commoditygroup = 'POTABLE'))

------------------------------
Julia Scott
Sarasota County Government

Original Message:
Sent: 08-28-2024 10:48
From: Matt F
Subject: Where Clause in SR app that pulls related work orders with a persongroup

Hey Julia,

Try this and let me know if it gives you what you're expecting:

((status = 'QUEUED') and exists   (select 1    from relatedrecord rel    join workorder wo on rel.relatedreckey = wo.wonum    where rel.recordkey = ticketid      and rel.relatedrecclass = 'WORKORDER'      and wo.persongroup = 'group_name_here'))

Cheers,

------------------------------
Matt F

Original Message:
Sent: 08-27-2024 22:12
From: Julia Scott
Subject: Where Clause in SR app that pulls related work orders with a persongroup

I am trying to set up a portlet that would show SRs that have a related workorder with a particular workgroup on the related workorder.

I know I want to use this as the base of the where clause---how do I add in the criteria for a particular persongroup on the workorder?

((status = 'QUEUED' ) and (exists (select rel.recordkey from relatedrecord rel where rel.recordkey = ticketid and rel.relatedrecclass = 'WORKORDER'))) 

#Administration
#MaximoApplicationSuite
#WorkManagement

------------------------------
Julia Scott
Sarasota County Government
------------------------------

Hey Julia,

Is there anything else formatting the whereclause incorrectly? Can you check your logs? Even when I test yours as is, I get the standard message of: 

BMXAA4186E - No records were found that match the specified query

Which means that it's formatting and executing properly, just not returning results as I have none which match your search criteria.

------------------------------
Matt F

Original Message:
Sent: 08-28-2024 11:12
From: Julia Scott
Subject: Where Clause in SR app that pulls related work orders with a persongroup

Hi---thank you, Matt.  I am trying the following but am getting the error:  BMXAA6713E - The MBO fetch operation failed in the mboset with the SQL error code 207. The record could not be retrieved from the database. See the log file for more details about the error.

I can run the where clause in SSMS and get results

(status <> 'RESOLVED')
and 
(exists 
  (select 1 
   from relatedrecord rel 
   join workorder wo on wo.wonum = rel.relatedreckey 
   where rel.recordkey = ticketid 
   and rel.relatedrecclass = 'WORKORDER'
   and wo.persongroup = 'ITPM' 
   )
)
and 
((commoditygroup = 'WASTEWATER' or commoditygroup = 'POTABLE'))

------------------------------
Julia Scott
Sarasota County Government

Original Message:
Sent: 08-28-2024 10:48
From: Matt F
Subject: Where Clause in SR app that pulls related work orders with a persongroup

Hey Julia,

Try this and let me know if it gives you what you're expecting:

((status = 'QUEUED') and exists   (select 1    from relatedrecord rel    join workorder wo on rel.relatedreckey = wo.wonum    where rel.recordkey = ticketid      and rel.relatedrecclass = 'WORKORDER'      and wo.persongroup = 'group_name_here'))

Cheers,

------------------------------
Matt F

Original Message:
Sent: 08-27-2024 22:12
From: Julia Scott
Subject: Where Clause in SR app that pulls related work orders with a persongroup

I am trying to set up a portlet that would show SRs that have a related workorder with a particular workgroup on the related workorder.

I know I want to use this as the base of the where clause---how do I add in the criteria for a particular persongroup on the workorder?

((status = 'QUEUED' ) and (exists (select rel.recordkey from relatedrecord rel where rel.recordkey = ticketid and rel.relatedrecclass = 'WORKORDER'))) 

#Administration
#MaximoApplicationSuite
#WorkManagement

------------------------------
Julia Scott
Sarasota County Government
------------------------------

Good call.  We just had the SCCD piece removed and perhaps there are some broken relationships.  It would seem to me that if it works in SQL, that it would work in Maximo.  We are currently in 761 moving to MAS8 soon.  I do appreciate your time!

------------------------------
Julia Scott
Sarasota County Government

Original Message:
Sent: 08-28-2024 11:25
From: Matt F
Subject: Where Clause in SR app that pulls related work orders with a persongroup

Hey Julia,

Is there anything else formatting the whereclause incorrectly? Can you check your logs? Even when I test yours as is, I get the standard message of: 

BMXAA4186E - No records were found that match the specified query

Which means that it's formatting and executing properly, just not returning results as I have none which match your search criteria.

------------------------------
Matt F

Original Message:
Sent: 08-28-2024 11:12
From: Julia Scott
Subject: Where Clause in SR app that pulls related work orders with a persongroup

Hi---thank you, Matt.  I am trying the following but am getting the error:  BMXAA6713E - The MBO fetch operation failed in the mboset with the SQL error code 207. The record could not be retrieved from the database. See the log file for more details about the error.

I can run the where clause in SSMS and get results

(status <> 'RESOLVED')
and 
(exists 
  (select 1 
   from relatedrecord rel 
   join workorder wo on wo.wonum = rel.relatedreckey 
   where rel.recordkey = ticketid 
   and rel.relatedrecclass = 'WORKORDER'
   and wo.persongroup = 'ITPM' 
   )
)
and 
((commoditygroup = 'WASTEWATER' or commoditygroup = 'POTABLE'))

------------------------------
Julia Scott
Sarasota County Government

Original Message:
Sent: 08-28-2024 10:48
From: Matt F
Subject: Where Clause in SR app that pulls related work orders with a persongroup

Hey Julia,

Try this and let me know if it gives you what you're expecting:

((status = 'QUEUED') and exists   (select 1    from relatedrecord rel    join workorder wo on rel.relatedreckey = wo.wonum    where rel.recordkey = ticketid      and rel.relatedrecclass = 'WORKORDER'      and wo.persongroup = 'group_name_here'))

Cheers,

------------------------------
Matt F

Original Message:
Sent: 08-27-2024 22:12
From: Julia Scott
Subject: Where Clause in SR app that pulls related work orders with a persongroup

I am trying to set up a portlet that would show SRs that have a related workorder with a particular workgroup on the related workorder.

I know I want to use this as the base of the where clause---how do I add in the criteria for a particular persongroup on the workorder?

((status = 'QUEUED' ) and (exists (select rel.recordkey from relatedrecord rel where rel.recordkey = ticketid and rel.relatedrecclass = 'WORKORDER'))) 

#Administration
#MaximoApplicationSuite
#WorkManagement

------------------------------
Julia Scott
Sarasota County Government
------------------------------