One of the challenges Maximo administrators face is getting a good user to get to the next level of experience in using Maximo. The biggest gain I think a power use can make is being able to transition from searches using the List Tab/Advanced Search to modifying searches in the Where Clause directly. Learning this skill opens the door to what a power user can search for within the Maximo application and what reports can be generated using the built in QBR reporting feature.
Learning EXISTS SQL statements
The most powerful SQL statement a Maximo user can learn is the EXISTS statement. 1 What the EXISTS statement does is check to see if a selection statement for the existence of rows. If there are, then the reset of the where clause will also return results. The biggest challenge that comes from learning SQL inside Maximo is learning and seeing when an error took place. For example, the following query appears to be correct:
(historyflag = 0 and siteid = 'ACME') and (exists (select 1 from dbo.poline where ((reportedby = 'STARKT01')) and (ponum=po.ponum and revisionnum=po.revisionnum and siteid=po.siteid)))
Running this query will result in the following error message:
Yeah... that's not very helpful.
The correct syntax should replace the reportedby with requestedby.
(historyflag = 0 and siteid = 'ACME') and (exists (select 1 from dbo.poline where ((reportedby = 'STARKT01')) and (ponum=po.ponum and revisionnum=po.revisionnum and siteid=po.siteid)))
Teaching points in Maximo
Learning EXISTS statements from inside Maximo can be done using several applications. All of the examples below were generated from entering information entered into the Advanced Search screen. Maximo then generated the SQL queries that can be viewed from the 'Where Clause' dialog box, including the EXISTS statements. These are being shown to demonstrate where a user can see and learn how to write a more complex SQL query.
Inventory: Item Description The Inventory application is setup where several fields are actually on other database tables. For example, the INVENTORY table does not directly store the Description of a part, that comes as a reference from the ITEM table. When you open Inventory, you can enter text into the Description field and the actual SQL query looks like this:
(status != 'OBSOLETE' and siteid = 'ACME') and (exists (select 1 from dbo.item where ((description like '%BEARING%')) and (itemnum = inventory.itemnum and itemsetid = inventory.itemsetid)))
Maximo already knew that the INVENTORY.DESCRIPTION field came from the ITEM table, so Maximo created the EXISTS statement to find any record that included the word bearing in the description.
Purchase Order: PO Line Details Purchase order history is one of the best places a power user can shine. Finding that one PO the facility did 2 years ago for that widget they bought can make a Maintenance Manger's day. But finding that one purchase order most likely includes search for details from a PO Line, not the PO itself. In this example the Advanced Search fields included a description of the part and who requested the part.
(siteid = 'ACME' and totalcost > 1000000.0) and (exists (select 1 from dbo.poline where ((description like '%WIDGET%' and requestedby = 'PYMMH01')) and (ponum=po.ponum and revisionnum=po.revisionnum and siteid=po.siteid)))
This query includes multiple items in the EXISTS statement to search for.
Work Order: Location Hierarchy Work orders have a lot of referential information. One of the strongest search features in WO Tracking is finding work orders based on a Location Hierarchy.
((woclass = 'WORKORDER' or woclass = 'ACTIVITY') and historyflag = 0 and siteid = 'ACME' and istask = 0) and (exists (select 1 from dbo.multiassetlocci where (exists (select 1 from dbo.locancestor where ((ancestor = 'ILIAD')) and (location =multiassetlocci.location and systemid = ( select systemid from locsystem where primarysystem = 1 and siteid =multiassetlocci.siteid) and siteid=multiassetlocci.siteid))) and (recordkey=workorder.wonum and recordclass=workorder.woclass and worksiteid=workorder.siteid)))
Whoa! This one has an EXISTS statement inside of another EXISTS statement. This is a great query to break down and show how sub-selections can work for your Maximo users.
Breaking down an EXISTS statement
While the examples above show what can generated from the Advanced Search tab, understanding the syntax of the queries created is how a power user can learn to adapt. The best approach is look at one of the simpler queries that utilizes an EXISTS. Let's use the query we first saw.
(historyflag = 0 and siteid = 'ACME') and (exists (select 1 from dbo.poline where ((reportedby = 'STARKT01')) and (ponum=po.ponum and revisionnum=po.revisionnum and siteid=po.siteid)))
Maximo will break Advanced Search queries down into parts. In this case we have two parts:
(historyflag = 0 and siteid = 'ACME')
and
(exists (select 1 from dbo.poline where ((reportedby = 'STARKT01')) and (ponum=po.ponum and revisionnum=po.revisionnum and siteid=po.siteid)))
The first part of the query looking for any purchase order that is not in history (historyflag=0) and was issued from the ACME site (siteid='ACME). There's a good chance this would return quite a few records. So the EXISTS statement puts a qualifier on what information is return to the Maximo user. The EXISTS statement looks to see of the rows that would be returned, which ones had a reportedby='STARKT01'. The results returned to a Maximo user would only include the purchase orders that were requested by user 'STARKT01'.
You can do the reverse of this by including a NOT modifier in front of the EXISTS statement. So using the same query, we can modify it like this:
(historyflag = 0 and siteid = 'ACME') and ( not exists (select 1 from dbo.poline where ((reportedby = 'PARKERP01')) and (ponum=po.ponum and revisionnum=po.revisionnum and siteid=po.siteid)))
In this example, Maximo would return any open purchase order, for the 'ACME' site, that was not requested by user 'PARKERP01'.
Understanding information relationships
Beyond the examples Maximo can generate itself, a power user will want to be able to understand how other tables are related to each other - e.g. PM to Job Plan, Work Order to Planned Materials, etc. The Database Configuration app has a tab that includes the steps Maximo uses to understand the relationship between different tables. Shown below is a sample of how Maximo would relate information from a parent Asset record to a child record.
The formatting of the 'Relationship Clause' is pretty straight forward.
ponum=:ponum and revisionnum=:revisionnum and siteid=:siteid
To use that relationship sequence, all of the : characters have to be changed to match the parent table that is being referred to. In this case the relationship sequence is referring back to the PO parent. So the relationship sequence would have to be written as:
ponum=po.ponum and revisionnum=po.revisionnum and siteid=po.siteid
Note how all of the : characters were replaced with po.. So using the advanced search in the Purchasing app, we can use an example search to create a starting block of code:
(historyflag = 0 and siteid = 'ACME') and (exists (select 1 from dbo.poline where ((requestedby = 'STARKT01')) and (ponum=po.ponum and revisionnum=po.revisionnum and siteid=po.siteid)))
So a Maximo user could start adding on other pieces of information that aren't present in the Advanced Search dialog box, but is present on the PO Line.
(historyflag = 0 and siteid = 'ACME') and (exists (select 1 from dbo.poline where ((requestedby = 'STARKT01' and catalogcode='MARK 42' and origid != 'SHIELD')) and (ponum=po.ponum and revisionnum=po.revisionnum and siteid=po.siteid)))
Out of the box, catalogcode and orgid are not visible in Advanced Search or in the PO Line detail section.
Relationship Data Report
I've included two versions of the relationships in Maximo. The Primary version only includes the main table/apps - WO, PO, Asset, Inventory, etc. The Extended version includes every relationship in the Database Configuration tab.