AnsweredAssumed Answered

Is there a PI SQL function to strip out the GUID and PointID from a PI Point data reference for query?

Question asked by RickSmithJr on Jul 14, 2018
Latest reply on Jul 16, 2018 by RickSmithJr

I am using PI SQL to retrieve the element hierarchy configuration for some testing.

The element attribute configString returns \\<PIServer>?<GUID>\<PITab>?<PIPointID>.


Result set -> \\myServer?fe623086-f68c-4b35-9a79-68f8fc62c8dd\PLUGH.PV?126043

What I want -> \\myServer\PLUGH.PV


SELECT eh.Level as ehLvl, eh.path + eh.Name Element, coalesce(ea.Level,0) as aeLvl, ea.path+ea.Name Attribute, p. Name as [Data Reference], ea.configString

FROM [Testing-Rick Smith].Asset.ElementHierarchy eh

LEFT JOIN [Testing-Rick Smith].Asset.ElementAttribute ea ON ea.ElementID = eh.ElementID

LEFT OUTER JOIN [System].[AF].[PlugIn] p ON ea.DataReferencePlugInID = p.ID

WHERE eh.Path LIKE N'\myMill\Pulp Mill\K1\%'

ORDER BY eh.path+eh.Name,ea.path+ea.Name+'\'