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.