Search for elements by attribute values using Simplified Asset Database E-R Model

This query searches for all Boiler elements where the value of the Manufacturer attribute is equal to KPC, Limited.

SELECT eh.Path + eh.Name Element FROM NuGreen.Asset.ElementTemplate et
INNER JOIN NuGreen.Asset.ElementTemplateAttribute eta ON
eta.ElementTemplateID = et.ID
INNER JOIN NuGreen.Data.Snapshot s ON
s.ElementTemplateAttributeID = eta.ID
INNER JOIN NuGreen.Asset.ElementAttribute ea ON
s.ElementAttributeID = ea.ID
INNER JOIN NuGreen.Asset.ElementHierarchy eh ON ea.ElementID =
eh.ElementID
WHERE et.Name = 'Boiler'
AND eta.Name = 'Manufacturer'
AND s.ValueStr = 'KPC, Limited'
OPTION (FORCE ORDER)

When performing queries on the Data.Snapshot table, you need to follow some best practices for internal optimization mechanisms to take place. Specifically, attributes must have the following configuration in the element templates:

• The Value Type is set to one of the basic types or an enumeration set (that is, not an array or object).

• The Indexed check box is selected.

• No data reference is used (the Data Reference field is set to <None>).

For more information on these types of queries, see the PI SQL Query Compendium included in the PI SQL Commander Lite tools.

The same optimization mechanisms take place when using transpose function tables:

SELECT eh.Path + eh.Name Element, tc.*
FROM [NuGreen].[DataT].[ft_TransposeSnapshot_Boiler] tc
INNER JOIN [NuGreen].[Asset].[ElementHierarchy] eh ON eh.ElementID = tc.ElementID
WHERE tc.[Manufacturer] = 'KPC, Limited'
OPTION (FORCE ORDER, IGNORE ERRORS, EMBED ERRORS)