AnsweredAssumed Answered

Get data of EventFrame Attributes from OLEDB Enterprise

Question asked by Paurav Joshi Champion on Mar 7, 2018
Latest reply on Mar 8, 2018 by Paurav Joshi

Hi Team,

 

We are trying to get attribute values of eventframe using SQL commander, seems straightforward but got stuck.

 

There are 14 attributes of EF and among them, 13 are PI tags and 1 is AF attribute. Out of 13 PI tags, 11 has been configured as below with relative time -1s, one with +1s and left one is @ Start Time in TimeRange:

 

How should we create a query in SQL commander that will pull out EF with all attribute values?

 

I have tried as follows and yet to find success:

 

Try 1:

SELECT ef.Name,ef.StartTime,efa.Name,ea.Value,ea.Time,ef.endTime
FROM 
(
-- Get EventFrame
SELECT *
FROM [TEST].[EventFrame].[EventFrame] 
WHERE Name = '<eventframe_name>'
) ef
-- Get Attributes of EF by comapring ID
INNER JOIN [TEST].[EventFrame].[EventFrameAttribute] efa ON efa.EventFrameID = ef.ID
INNER JOIN [TEST].[Data].[EventFrameArchive] ea ON ea.EventFrameAttributeID = efa.ID
WHERE ea.Time BETWEEN ef.StartTime-time('10s') AND ef.EndTime
OPTION (FORCE ORDER, EMBED ERRORS)


 

O/P 1:

2 attributes of EF - an AF attribute and PI Tag @ StartTime

Ideally it should give all 14 attributes values.

 

Try 2:

used modified version of Querying event frame related AF attribute historical data with time offset

SELECT ea.Name attribute, d.Value value, d.Time time
            FROM [TEST].[EventFrame].[EventFrame] ef
            INNER JOIN [TEST].[Asset].[Element] el ON el.ID = ef.PrimaryReferencedElementID
            INNER JOIN [TEST].[Asset].[ElementAttribute] ea ON ea.ElementID = el.ID
            INNER JOIN [TEST].[Data].[Archive] d ON d.ElementAttributeID = ea.ID
            WHERE ef.Name ='<EventFrame_Name>' AND 
            d.Time BETWEEN ef.StartTime-time('1s') and ef.StartTime+time('1s') 
ORDER BY ea.Name
            OPTION (FORCE ORDER, EMBED ERRORS)

 

O/P 2:

Giving partly element attributes which are in EF and which are not both.

 

Roger Palmen seems you have worked lot on EF in 2016-17. If you can guide where something is wrong will be helpful .

 

Thanks,

Paurav Joshi

Outcomes