Asking for help.. too tired to think.
I need a query that can executed in SQL Commander to find a list of elements by a given template name.
Ultimately, I have 4 templates it will need to be executed against.
Thank you in advance...
Good question! You'll have to utilize an INNER JOIN between the Asset.Element table and the Asset.ElementTemplate table in order to filter by ElementTemplate name. Here's an example pulling all the contents of Asset.Element where the ElementTemplate is called 'RecalcTemplate'. You can customize it as needed.
FROM [2016R2].[Asset].[Element] A
INNER JOIN [2016R2].[Asset].[ElementTemplate] B ON A.ElementTemplateID = B.id
WHERE B.name = 'RecalcTemplate'
Thank you.. I am working from your sample.
As an aside, is there any way a document could be posted showing the joins between the AF tables?
I'm sure others would benefit from it as well.
I'm glad the sample is working out for you. There isn't an official OSIsoft document showing the joins between the AF tables. Although it's a finite number, there are a lot of different ways to join the various asset tables.
Hint: typically any column name ending in ID (e.g. ElementTemplateID) can be joined on the corresponding ID column (ElementTemplate.ID). You can look at the unique keys in PI SQL Commander as well.
ElementTemplateID to ElementTemplate.ID - that is the key I was looking for.
Retrieving data ...