• Adi Jaradat

Maximo Where Clause

Updated: Jun 21

Maximo has powerful search functionality in the form of “Advanced Search”, “Attributes Search”, “List Tab”, and amazingly versatile search operators. However, there are cases where you need to apply rather complex queries which may not be easy to fit into any of the above tools.


Lockley, “Saved Queries” has become the “Go-To” approach to model complex cases or sometimes straightforward cases due its ease of use (sometimes). The idea behind “Saved Queries” is really drills down to how skillfully you can “squeeze” your intended logic into the “where” part of the query.


A few clicks away from any application and you will be faced with intense use of “Where Clause” especially in the Database Configuration, Application Designer, Conditional Expressions, Relationships, and so on. If you are in the field of configuring and customizing Maximo you have undoubtedly written many where clauses.


Where clause should be kept as simple as possible, but once in a while you will be faced with the need to write rather complex one. In the simplified example below I am trying to get all Person records for people whose title is similar to “Operation” and to make my where clause easy to maintain, easy to troubleshoot, and be able to squeeze more into it I have used Recursive Queries.


--SELECT * FROM PERSON WHERE -- This line of code is controlled by Maximo
PERSONID IN (
WITH OPERATIONS_GROUP AS (
    SELECT P.PERSONID
          ,P.DISPLAYNAME
      FROM PERSON P
          ,TITLE T
     WHERE 1=1
       AND P.TITLE=T.TITLENUM
       AND UPPER(T.DESCRIPTION) LIKE 'OPERATIONS%'
)
    SELECT PERSONID
      FROM OPERATIONS_GROUP
     WHERE 1=1
)
 

Links

https://adijaradat.com/post/follow-up-work-orders/

https://www.ibm.com/support/knowledgecenter/en/SSLKT6_7.6.0/com.ibm.mt.doc/gp_ui/c_SQL_search.html

15 views0 comments

Recent Posts

See All