PIOLEDB Enterprise Help

Discussion created by Halenger Champion on Jul 30, 2012
Latest reply on Aug 2, 2012 by hanyong



I have templates in AF for a meter of a system (Domestic Water, Chilled, Water, Steam, etc.) as well as having a Building template.  So my heirarchy is to create a building and then add a meter template as a New Child Element (the buildings vary with number of meters and systems being metered).  In each meter template I have attributes like Maintenance Date and Comments... Under Comments, I have five other attributes as "Comments (1-5)" to give details of what a technician did to service the meter.  Each time he goes there and services it, I've been recording what was done/found under the next Comments (1-5) attribute.  So, I'd now like to beable to create a report for all the meters that have been worked on...


I've created table-valued functions for each template and can query (based off the sample queries in SQL Commander) all the attributes of a meter (template)... One meter at a time though...  I'd like to query all the meters I have in my AF system depending on the meter type template to report maintance dates and info.  Below is a snapshot of my AF System:






I can query a meter by giving a path to the repective building... But I want to go through all buildings and query only the Comments and Maintance Date attributes.  Furthermore, when I query, I'm only retrieving the Comments attribute and none of the Comments (1-5) attributes.  The code I've been tinkering with is as follows:

SELECT eh.Path + eh.Name Element, ts.*
FROM Metering.Asset.ElementTemplate et
INNER JOIN Metering.Asset.Element e ON e.ElementTemplateID = et.ID
INNER JOIN Metering.Asset.ElementHierarchy eh ON eh.ElementID = e.ID
CROSS APPLY Metering.DataT.[TransposeSnapshot_Domestic Water Meter Template] (eh.ElementID) ts
WHERE eh.Path = N'\Buildings\Babbidge Library\'



Thanks for any help!