One of the top prizes for any Maximo users is having that "special" query that they know will absolutely find everything they are looking for. Recently I was asked to get a list of every PM in part of the a plant. The Maintenance Manager wanted the list to include PM's against Locations, Assets, and/or Routes. He complained that the only way he'd been able to previously get the information was by getting four different lists and trying to merge them together in Excel.

I asked him what he really needed and the quick reply was, 'I need all the PM's'. So I asked him what does his packet of information look like ¹ when we get the query to work right. We sat down and looked at what he was trying to do and came up with the following:

• Get all PM's of Locations within the Location hierachy of an area of the plant.
• Get all PM's of Assets belonging to Locations within the Location hierarchy of an area of the plant.
• Get all PM's that were part of Route Stops that had Locations or Assets within the Location hiearchy of an area of the plant.

This quick back and forth took no more than 2 minutes after we both slowed down to look at what was needed. We then started tackling each bullet item to get a portion of his overall search query. From there we can glue them all together for a final query.

Location Hierarchy

The first one we tackled was all of the Locations in the area of the plant. The Maintenance Manager had first tried using the "Search Location Hierarchy". I love this box because it can give just about any Maximo user a built-in demonstration of a sub-select query in Maximo. Because the Location Hierarchy and Location Parent records reside on tables, other than the LOCATIONS table, Maximo will give the sub-select query if a user looks at the Where Clause in the Advanced Search.

After determining where in the plant his was focused on, we ended up with the following query:

siteid = 'BEDFORD' and exists(select 1 from locancestor where ancestor = 'UTILITIES' and siteid = pm.siteid and location=pm.location)

Asset Hierarchy

The next one we tackled was getting all the PM's for any assets that belonged to a Location within the plant area. This one got trickier because while the PM module can search by PM.ASSETNUM, it won't allow you to search for assets related to Locations in a hierarchy. For this step I explained we needed to use an IN SQL statement. The IN statement basically returns records found "in" a given list of related records. But remember, we're looking for Assets in a Location in a Location Hierarchy. Notice I needed to use the work "in" twice? That means we'll need to use a nested IN statement with our query. After working with him a few minutes we came up with the following:

siteid = 'BEDFORD and assetnum in (select assetnum from asset where siteid=pm.siteid and location in (select location from locancestor where ancestor = 'UTILITIES' and siteid = asset.siteid and location=asset.location))

Routed Locations & Assets

The last step was to get PM's that use a Route and the Route Stop has a Location in the hierarchy or is associated with an Asset in a Location in the hierarchy. As soon as he made the declaration of needing "this" or "that", it was a tip that this last query may actually be two. When we further broke down the last bullet item it turns out we needed two queries. Why? Because Route Stops can be either based on a Location or an Asset. After a little discussion we figured that we needed to get a list of Route Stops with Locations in the Location Hierarchy and Assets in Route Stops in Locations in the Location Hierarchies. That's going to require two queries that have multiple IN statements.

After some quick work we came up with the following:

siteid='BEDFORD' and (route in (select route from route_stop where siteid=pm.siteid and location in (select location from locancestor where ancestor = 'UTILITIES' and siteid = route_stop.siteid and location = route\_stop.location))or route in (select route from route_stop where siteid=pm.siteid and assetnum in (select assetnum from asset where siteid=route_stop.siteid and location in (select location from locancestor where ancestor = 'UTILITIES' and siteid = asset.siteid and location=asset.location))))

Final Query

Gluing the three queries all together got us this:

siteid='BEDFORD' and (
exists(select 1 from locancestor where ancestor = 'UTILITIES' and siteid = pm.siteid and location=pm.location)
or assetnum in (select assetnum from asset where siteid=pm.siteid and location in (select location from locancestor where ancestor = 'UTILITIES' and siteid = asset.siteid and location=asset.location))
or route in (select route from route\_stop where siteid=pm.siteid and location in (select location from locancestor where ancestor = 'UTILITIES' and siteid = route\_stop.siteid and location=route\_stop.location)) 
or route in (select route from route\_stop where siteid=pm.siteid and assetnum in (select assetnum from asset where siteid=route\_stop.siteid and location in (select location from locancestor where ancestor = 'UTILITIES' and siteid = asset.siteid and location=asset.location))) )

From here anyone can substitute either the Locancestor value to find other PM records or modify the usage of the PM tables details for use in another app - e.g. find all work orders in an area of a plant.

Note: Your browswer may change the formatting of the query above and change the single quotes to "smart quotes" ²

Summary

When tackling complex search queries, try to take a step back and break down what you're trying to find:

• Write a sentence, or two, that coud explain to someone else what you're trying to search for. If your co-worker can't figure out what you mean, you may be missing some details.
• Look for keywords like in, with, or, and in your sentence. This can indicate what kind of query you may need to create.
• Break down your queries into segments and then "glue" them back together to make the more complex query. Going for a home run on the first attempt means you'll most likely end up with a BMXAA4210E - Database error number 1234 has occurred error.