Suppose that, in your organization, you have open and closed work history events. Each Work History record contains an Order System Status field, which is used to record the status of that work history event. You want to see how many open work history events you have and how many closed work history events you have. Instead of the stored values of CLSD TECO or OPEN, however, you want to see the values Closed or Open in your query results.
In addition, you are concerned that not all Work History records contain a value in the Order System Status field. You also want to see in your query results which records do not have a value in this field so that you can update those records.
In this case, you might configure a query on the Work History family, and then add the Expr and Order System Status fields to the query.
In this example, to return the status of each work history event as Closed, Open, or No Status (meaning that the record does not contain a value in the Order System Status field), you can configure an expression using the DECODE function.
In this case, the expression syntax is:
This syntax indicates that for Work History records that contain the value CLSD TECO in the Order System Status field, you want to return the value Closed. For those records with the value OPEN in the Order System Status field, you want to return the value Open. For records where the Order System Status field is empty, you want to return the value No Status.
You would also add a COUNT function on the Order System Status field so that you can see the number of work history events that fall into each category.
Other Possible Uses
- You want to review the types of maintenance activities that are being performed in your plant and the total number of failures that resulted in each type of activity. Users can enter any value they choose into a field that tracks maintenance activities, so you know that your users are using different terminology to mean the same thing. For example, to indicate that they replaced broken components, different users might enter replace, replacing, or replacement. You can write a DECODE statement to indicate that the values replace, replacing, and replacement should return the value Replace in the query results.
- Members of management want to investigate failures that resulted in a failure cost of $50,000 or more. You can write a DECODE statement to indicate that failures with a failure cost greater than or equal to $50,000 should return the value Please Investigate in the query results. Other failures should return the value OK in the query results.