Function GetPIPoint in OLEDB Enterprise.

Discussion created by HDelesalle on Aug 29, 2013
Latest reply on Sep 5, 2013 by petrk75





The GetPIPoint function in the Asset catalog of a PI-AF database seen through PI OLEDB Enterprise is quite convenient to acess the Tag mapping of an attribute.


This function gives the Server name , tag name and whole path from the PIPoint Configstring of a given attribute.


However, it would be very useful to return also the UOM from the Configstring as an extra column.


This is the only place to find the PI-AF UOM related to the tag.


We do need that information to write information into to the PI Point with the proper unit, especially when the engunit is not properly or uniformly defined in the PI Server.


We hope that the GetPIPoint function will be enhanced in the future to provide this.


In the meantime, one can decode the whole configstring that can be retrieved through the ElementAttribute table.


We have done that with the code below.


However, does anyone have ever developped a more robust function that can deal with specific cases like the case when the UOM is not entered  or configstring is empty ?


Thanks for your help




SELECT e.Name, ea.Name, gpp.Tag, CASE  WHEN CHARINDEX(';', ea.ConfigString, CHARINDEX('UOM=', ea.ConfigString)) > 0 THEN LEFT(RIGHT(ea.ConfigString, LEN(ea.ConfigString) - CHARINDEX('UOM=', ea.ConfigString) -3), CHARINDEX(';', ea.ConfigString, CHARINDEX('UOM=', ea.ConfigString)) - CHARINDEX('UOM=', ea.ConfigString) -4)  ELSE RIGHT(ea.ConfigString, LEN(ea.ConfigString) - CHARINDEX('UOM=', ea.ConfigString) -3)