This document contains the answers to: Exercise: Creating Custom Functions with the RTQP Engine.
Create a custom function that provides the Plant name for any element in the NuGreen AF Database, aside from the root 'NuGreen' element itself (as this does not have any associations with a specific plant).
CREATE FUNCTION Master.Element.GetPlantName ( @ElementID Guid ) AS SELECT Value Plant FROM Master.Element.Attribute WHERE ElementID = @ElementID AND Name in ('Plant','PlantCity')
CREATE FUNCTION Master.Element.GetPlantName2 ( @ElementID Guid ) AS SELECT SUBSTRING(Path+Name+'\\',CHARINDEX('\',Path+Name+'\\',2)+1,CHARINDEX('\',Path+Name+'\\',CHARINDEX('\',Path+Name+'\\',2)+1)-CHARINDEX('\',Path+Name+'\\',2)-1) Plant FROM Master.Element.ElementHierarchy WHERE ElementID = @ElementID
When creating a function, it's best to start with a standard SQL query that has an ID hard coded as a test, then once it's working, extract everything out to variables as the CREATE FUNCTION header is built.
So we'll start with exploring the NuGreen database in PI System Explorer to see what approaches we could take in our SQL query. There's two that come to mind for me:
1. The elements based on templates derived from 'Unit', as well as elements based on the 'Process' template, have a 'Plant' attribute that contains a space in the name if necessary (eg, 'Little Rock')
The elements based on the 'Plant' template have attributes named 'PlantCity' that contains a space and 'PlantTruncatedName' that does not.
One pitfall of this approach is that the elements named 'Equipment', located as a subelement to any 'Process' element, does not contain any attributes:
2. The name of the plant is always the second part of the element path ('\NuGreen\<plant name>\...'). In the case of the plant element itself, it's the second part of the path+name:
Note: There are certainly other ways to accomplish this; this solution guide will only demonstrate two methods but you are welcome to try any other approach you would like.
Let's start with the first approach since this is fairly clean and mostly gets the job done (it only excludes the 'Equipment' elements). We'll write a query that get's the value of the Plant or PlantCity attribute, given an ElementID. The ElementID's we'll use will be the result of the following two queries, to make sure we have a representative ElementID of an element that uses PlantCity and one that uses Plant as the attribute:
Since the Master.Element.Attribute table contains snapshot values of all attributes, as well as ElementID, we don't need to perform any joins:
We can even test this query against either the main NuGreen element or any of the Equipment elements to confirm that no errors appear, just an empty result set:
Now that we have the query working, we can extract out the hard coded ElementID and replace it with a variable that's set in the function parameters list (as well as altering the SELECT statement to just return the value):
Note: We know the datatype of the @ElementID parameter from looking at the Master.Element.Attribute columns in the Object Explorer:
Once we've created the function, we can refresh the list of Table-Valued Functions in Master > Element, and confirm that it was created:
As a test, we can cross apply this function to the entire Master.Element.Element table to confirm that it works as expected:
This output only contains 131 rows, as the 'Equipment' and 'NuGreen' elements drop out automatically from the result city due to their not having a 'Plant' or 'PlantCity' attribute. Despite this, this is a very simple way cover most of the requested case. If this were a real life scenario instead of an exercise, the best approach may be to add an attribute to the 'Equipment' elements to track their plant names, if the result set actually requires them to be present.
If we can't change the attributes present on these elements and they are required to be present in the result set, we can proceed with the second approach, which is to parse into the path of the element to find the plant name.
If we look at the output of the path of each element, we can see that it's always in the form of '\Nugreen\<plant name>\...', except for the name 'Plant' elements themselves, where it's the name of the element:
Because of this regularity, we can use the SQL string scalar-functions to parse out the string in between the second the third '\' character of Path+Name+'\' (to be sure to catch the 'Plant' elements as well). We'll use the same approach of starting with a hard coded ElementID and extracting out from there to create the function, and we'll also start with the same B-344 ElementID from the first approach:
First, we'll isolate the specific element by its ID and output the full name of the Path+Name+'\':
Next, we'll create a second column that's just the plant name using the substring function, hard coded for the right number of characters:
Next, we'll use the charindex function to find the location of the second '\'. Since we know that the path will always start with a '\', if we start looking for a '\' at the second character, it will find the second '\' every time:
Note: We add 1 to the result so that we start from the character following the second '\'
Next, we'll calculate the length of the plant name by finding the location of the third '\' (charindex from the character following the second '\') minus the index of the second '\' character.
If this last step was too much to follow at once, we can break it down into each small piece in a portion of the select statement to better visualize it:
To create the function, we can extract out the hard coded ElementID and replace it with an @ElementID parameter in the function header (as well as change the SELECT statement to only output the value):
And we can refresh the table-valued functions list in the Object Explorer to confirm it was created:
To test it, let's CROSS APPLY this function against all elements in the NuGreen database, aside from the 'NuGreen' root element itself. Notice that the 'Equipment' elements are now also tagged with the correct plant they belong to, and there's a total of 142 elements in the output:
One final thing to note is what happens on this approach when NuGreen is in fact added to the list. We see the error message "Invalid start position":
This is because the SELECT statement is hardcoded to look for three separate '\' characters, but Path+Name+'\' for the root 'NuGreen' element only contains two '\'s.
To make this function more robust, and to have it output an empty string for the plant name instead of throwing an error message for 'NuGreen', we can add an addition trailing '\' to Path+Name+'\'. This way, a third '\' is always found, and when it parses the NuGreen path, it won't throw an error:
And we can execute an ALTER FUNCTION query to commit this change to the function itself:
Now, if we refresh the query that previously errored out, we will see an additional row, as well as the 'NuGreen' element successfully outputted with an empty string for the plant name:
Obviously this approach is a lot messier than the first approach, but is slightly more robust. It also shouldn't matter who messy the function is under the hood because it is very clean to execute the function within another query, as seen in the previous screenshot. Regardless of the approach taken, it's good to consider whether the query can be "broken" by passing it unexpected elements (such as 'NuGreen') and if there's an easy way to implement error handling.