Retrieve element attributes

SELECT eh.Path + eh.Name + ea.Path + ea.Name Path
FROM NuGreen.Asset.vElementHierarchy eh
INNER JOIN NuGreen.Asset.vElementVersion ev ON ev.ID = eh.ElementVersionID
INNER JOIN NuGreen.Asset.vElementAttribute ea ON ea.ElementVersionID = ev.ID
WHERE eh.Path LIKE N'\NuGreen\Houston\Cracking Process\Equipment\'
AND eh.Effective <= N'01-Aug-2011' /*query date*/
AND eh.Ineffective > N'01-Aug-2011' /*query date*/
AND eh.Name LIKE N'B-%'

PI OLEDB Enterprise may fail to prepare optimal execution plan for more complex join queries. Thus, it is better to use an OPTION (FORCE ORDER) clause and order tables in the FROM clause by the intended execution order. For example, the query above is expected to first retrieve elements from the hierarchy, then it should look for element version information and finally it should execute the element attribute query.