January 11, 2013

How to extract failure codes, problems, causes and remedies

Today a customer asked me to extract the full listing of failure codes, problems, causes and remedies. He wants to be able to create an Excel spreadsheet to analyze the failure codes tree in order to improve and reorganize such structure.
Here is the SQL query that can extract this information from Maximo database.

select f.failurecode failureclass, p.failurecode problem, c.failurecode cause, r.failurecode remedy
from failurelist f
left outer join failurelist p on p.parent=f.failurelist and p.type='PROBLEM'
left outer join failurelist c on c.parent=p.failurelist and c.type='CAUSE'
left outer join failurelist r on r.parent=c.failurelist and r.type='REMEDY'
where f.parent is null
order by f.failurecode, p.failurecode, c.failurecode, r.failurecode;

If you need to import failure codes hierarchy you may take a look at this article.

2 comments:

  1. This is great but I need it to also bring in the descriptions for all four columns.

    I am also looking for a way to query work orders for all of the failure codes so that I can start drilling down on what the problem assets are.

    ReplyDelete