MxLoader

Hi there,

I'm having an issue with creating queries that include specifications for my hierarchypath field.

my current SQL is; hierarchypath LIKE '%SUBSTATION \ NO MODIFIER%'

This is the error message; Error Code 400: BMXAA4210E - Database error number 207 has occurred. Invalid SQL queries are logged in the log file. Check the SQL query to identify the error.

My first thought was that this error maybe caused by the delimiter in the query term, but even terms without the delimiter are causing this error. The syntax of the query works perfectly when searching other fields, and 'hierarchypath' is being successfully used to pull values in the sheet that I'm trying this query on.

Does anyone have any suggestions on how to correct this? or even how to check the log file? I'm a newer user of MX Loader so I'm hoping this issue is due to my inexperience. 

Any advice is appreciated! Thank you.

Hierarchy path is a non-persistent attribute in Maximo. This means you can retrieve the data using object structures, but you can't filter on them since Maximo converts the filter into a SQL where clause. We've created views in the database that build out the hierarchy path which you could do here. Or you could create a relationship from your table to CLASSSTRUCTURE with something like:

classstructureid in (select classstructureid from classstructure where classificationid like 'NO MODIFIER%' and parent in (select classstructureid from classstructure where classificationid like '%SUBSTATION'))

I would caution if you have a lot of classifications, like statements like this aren't efficient. It might be worth adding a custom field instead to search on. 

Ahhh that makes a lot of sense, Steven. I really appreciate your explanation; it helped me with my understanding of MX Loader and Maximo both more than you know.

Thank you!

Hi Sophia,

Steven's observation is accurate that the hierarchypath is a non-persistent Maximo attribute, so the displayed value is not stored as a simple physical database column.

However, non-persistent does not mean impossible to query, validate, or reproduce. It means the value is derived through Maximo application logic. In Maximo's Database Configuration Application, the Maximo data dictionary, MBO relationships, and Java classes behind the asset catalog objects, that logic is related to the ASSETCATALOG Maximo Service. Please see the attached Maximo ERD. 

The classification hierarchy and specification configuration can be rebuilt from the persistent Maximo asset catalog and classification tables. Please see the attached queries, validated in SQL Server and Oracle databases, but need validation in DB2.

It is possible to further drill down into Maximo Objects to further validate data at the Object level. Let me know if you have any questions; I've used the logic extensively over the past eight years in DB2, Oracle, and SQL Server databases.

Key Maximo Objects, Java Classes, and Descriptions

Please use the SQL queries that rebuild and validate the classification hierarchy and specification configuration from these underlying Maximo objects.

These queries provide both summary and detail analysis for: I did not provide the query in the last column, the reference is an example of the ability to extend the logic to drill down into each classification's actual data, to draw meaningful insights. 

ASSET CATALOG ERD: 

CLASSIFICATIONS ERD: 

SQL SERVER QUERY EXAMPLE: 

Query Extendability ERD Reference: The Classification Tables below are a visualization of the expandability of the attached queries for the Asset ERD 


Best,

Attachment(s)

Maximo Classification SQL Queries For SQL Server - DB2 - ...pdf   272 KB 1 version