Using AFSDK Pass-through Search Queries in the RQTP Query Engine

Document created by kduffy on Dec 26, 2018Last modified by kduffy on Dec 26, 2018
Version 3Show Document
  • View in full screen mode

The RTQP Query Engine allows users to take advantage of the AFSDK's ElementSearch and EventFrameSearch classes.

 

These are exposed through the following two Table-Valued Functions:

  • Master.Element.FindElements(@Query)
  • Master.EventFrame.FindEventFrames(@Query)

 

They can be found in the Object Explorer window of PI SQL Commander:

 

And an example of the FindElements search can be found in the Query Compendium as the final query under PI SQL Client > Queries > Features.sql:

 

The query that can be provided to this function follows the syntax found in the AFElementSearch.FindElements's example query:

var search = new AFElementSearch(myDB, "FindValves", @"Root:'WestPlant\Area32' Category:'Valve'")

This query syntax is Key:"value" and space delimited between pairs.

 

The key value pairs being passed must be members of the AFSearchFilter Enumeration set, but also must pertain to the class being searched against (the enumeration set covers all search classes, not only Element and EF)

 

Example:

The following RTQP query will find the names of all Boiler elements in the Houston plant by joining the Element table (for the template name) with the ElementHierarchy table (for the element path):

SELECT e.Name
FROM Master.Element.Element e
INNER JOIN Master.Element.ElementHierarchy eh
ON e.ID = eh.ElementID
WHERE e.Template = 'Boiler'
AND eh.Path like '\Nugreen\Houston%'

 

 

The following RTQP query will return the same list of names, but using the AFSDK Search pass-through query, providing Template and Root arguments:

SELECT Name FROM Master.Element.FindElements('Template:"Boiler" Root:"Nugreen\Houston"')

 

Deciding which one to use should be done on a case by case basis, considering things such as SQL query complexity and AFSDK search complexity, and it may be a good idea to write the queries both ways to see if one is faster than the other before implementing it in a production environment.

Attachments

    Outcomes