Maximo Open Forum

Using the Object Structure MXCLASSIFICATION, I can include the non-persistent field HIERARCHYPATH. When this object structure and attribute get applied to a MxLoader sheet, I can query and return the hierarchy path.

What syntax does SQL Server use to return the hierarchy path as a field in the select statement? I want to use it in conjunction with the assets object.

Hierarchy path is a non-persistent built via Java classes. The classstructure table holds the references of all classifications and their parent (or null for the top level classifications). You then loop through to build out the hierarchy path. I don't have a SQL Server environment readily available but here's an example of how to do it on Oracle:

with cte(classstructureid,hierarchypath,parent) as(
select classstructureid,classificationid as hierarchypath, parent from classstructure where parent is null
union all
select x.classstructureid,cte.hierarchypath || ' \ ' || classificationid as hierarchypath,x.parent
from cte 
inner join classstructure x on x.parent=cte.classstructureid
)
select * from cte
;

Syntax is pretty similar for SQL Server except the || would be replaced with a + sign instead to concatenate the string. If you want to fetch the hierarchy path for a single classtructureid you can add a where clause to the select * from cte such as:

select * from cte where classstructureid='2351'

Using the Object Structure MXCLASSIFICATION, I can include the non-persistent field HIERARCHYPATH. When this object structure and attribute get applied to a MxLoader sheet, I can query and return the hierarchy path.

What syntax does SQL Server use to return the hierarchy path as a field in the select statement? I want to use it in conjunction with the assets object.

I found something similar to this and I believe it was authored by you. How would I incorporate this query from assets?

Hierarchy path is a non-persistent built via Java classes. The classstructure table holds the references of all classifications and their parent (or null for the top level classifications). You then loop through to build out the hierarchy path. I don't have a SQL Server environment readily available but here's an example of how to do it on Oracle:

with cte(classstructureid,hierarchypath,parent) as(
select classstructureid,classificationid as hierarchypath, parent from classstructure where parent is null
union all
select x.classstructureid,cte.hierarchypath || ' \ ' || classificationid as hierarchypath,x.parent
from cte 
inner join classstructure x on x.parent=cte.classstructureid
)
select * from cte
;

Syntax is pretty similar for SQL Server except the || would be replaced with a + sign instead to concatenate the string. If you want to fetch the hierarchy path for a single classtructureid you can add a where clause to the select * from cte such as:

select * from cte where classstructureid='2351'

Using the Object Structure MXCLASSIFICATION, I can include the non-persistent field HIERARCHYPATH. When this object structure and attribute get applied to a MxLoader sheet, I can query and return the hierarchy path.

What syntax does SQL Server use to return the hierarchy path as a field in the select statement? I want to use it in conjunction with the assets object.

If you're writing a report or just executing SQL directly against the database, you can join the CTE with the asset table like:

with cte(classstructureid,hierarchypath,parent) as(
select classstructureid,classificationid as hierarchypath, parent from classstructure where parent is null
union all
select x.classstructureid,cte.hierarchypath || ' \ ' || classificationid as hierarchypath,x.parent
from cte 
inner join classstructure x on x.parent=cte.classstructureid
)
select asset.*,cte.hierarchypath from asset
inner join cte on cte.classstructureid=asset.classstructureid
;

That will get me all assets that have a classification (since I chose to do an inner join) along with their hierarchy path.

If you're doing the search in Maximo and looking for a specific classification, the advanced search dialog in Maximo in the applications (such as Asset) have a Classification field that you can search on that takes the hierarchy path and turns it into a classstructureid automatically (though it's used to search for anything under the hierarchy which may or may not be what you want). 

At my previous job we created a view in the database so that we could easily query this without having to write it each time and it made searching easier as well. With a complex view like this you need to "import it" which means creating the view directly in the database and then creating a new object in Database Configuration with the exact same name. Database Configuration will see it exists and record it as an imported view. 

I found something similar to this and I believe it was authored by you. How would I incorporate this query from assets?

Hierarchy path is a non-persistent built via Java classes. The classstructure table holds the references of all classifications and their parent (or null for the top level classifications). You then loop through to build out the hierarchy path. I don't have a SQL Server environment readily available but here's an example of how to do it on Oracle:

with cte(classstructureid,hierarchypath,parent) as(
select classstructureid,classificationid as hierarchypath, parent from classstructure where parent is null
union all
select x.classstructureid,cte.hierarchypath || ' \ ' || classificationid as hierarchypath,x.parent
from cte 
inner join classstructure x on x.parent=cte.classstructureid
)
select * from cte
;

Syntax is pretty similar for SQL Server except the || would be replaced with a + sign instead to concatenate the string. If you want to fetch the hierarchy path for a single classtructureid you can add a where clause to the select * from cte such as:

select * from cte where classstructureid='2351'

Using the Object Structure MXCLASSIFICATION, I can include the non-persistent field HIERARCHYPATH. When this object structure and attribute get applied to a MxLoader sheet, I can query and return the hierarchy path.

What syntax does SQL Server use to return the hierarchy path as a field in the select statement? I want to use it in conjunction with the assets object.