Maximo Open Forum

 View Only

 When searching in asset module, must type status field in upper case, or no results are found.

  • Assets
  • End User
  • Functional
G Tyler Byrne III's profile image
G Tyler Byrne III posted 01-25-2022 10:24

I have observed a problem when entering "operating" or "inactive" etc., using lower case in the statues field search, and not returning results? Lower case can be used in all of the other asset search fields and return results. If uppercase "OPERATING" or "INACTIVE" etc. is entered, or selected in the dropdown, all results show up. Is there a known fix for this? I am an end user and most likely can't repair this, but can forward the information to the right folks.

Thanks for any assistance.

Tyler

App Server IBM WebSphere Application Server 9.0.5.10
                    Tivoli's process automation engine 7.6.1.1 Build 20190514-1348 DB Build V7611-365
                    IBM Maximo Asset Management Work Centers 7.6.0.4 Build 20190514-1348 DB Build V7604-119
                   CiM Visual Planner Suite (FUJI) 7.6.1.0 Build 20200310-1.0 DB Build V7610-4
Version     TRM RulesManager SE 5.8.0.2.2 Build 3372 DB Build V582-07
                   IBM Tpae Integration Framework 7.6.1.1 Build 20190419-2330 DB Build V7611-01
                   IBM Maximo Asset Management 7.6.1.1 Build 20190514-1348 DB Build V7611-01
                   IoT Connection Utility 7.6.0.2 Build 20190426-2206 DB Build V7602-07
Server OS Linux 3.10.0-1160.49.1el7.x86_64
Server DB Oracle 19.0 (Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0)

Attachment  View in library
Asset Module Search Issue.docx 35 KB
Steven Shull's profile image
Steven Shull posted 01-25-2022 11:37
Most status fields are defined as UPPER so Maximo would convert any value provided to UPPER before querying the database. Asset & Location status for some reason are set to ALN which means this cast to upper doesn't occur unless the search type is WILDCARD in the database (which it is by default). If someone changed this to EXACT search for these fields in Database Configuration then you would not get records unless you entered it in the exact case on Oracle/DB2. SQL Server is configured as case insensitive for Maximo so it is not impacted by this deviation. 

There is a good reason to have search type set to EXACT instead of WILDCARD. What happens in Maximo with a search type of WILDCARD on an ALN field is it generates a SQL query like:
upper(status) like '%OPERATING%'. Without function based indexes, by casting the status column to upper, indexes on the status column will not be utilized. If you have a large database that could cause really poor performance for the query execution.

Related Content