narburgh

PI JDBC query: get archived values for a time frame, outside bounds

Discussion created by narburgh on Mar 19, 2020

Hello,

I'm using PI SDL DAS with OLEDB Ent 2019 (we don't have AF 2018+ so I am unable to use RTQP as this time). What I'm trying to do is query archived values of attributes of a unit during the duration of an EF. I'm doing this through the query I've included below. The issue is some of the EFs are short enough that there aren't any archived values stored for some of the attributes. Is there a function or call or parameter I can use to get the previous/current archived value of the attribute, similar to how when using DataLink for compressed values you can choose inside/outside bounds?

Thanks,

Sarah

 

SELECT DISTINCT 

ef.ID eventFrameID,

ef.PrimaryReferencedElementID,

ef.Name EF, 

ef.StartTime,

ef.EndTime,

a.Name UnitAttribute, 

ar.Time UnitAttributeTime,

ar.Value UnitAttributeValue

FROM [AFDB].[EventFrame].[EventFrame] ef

INNER JOIN [AFDB].[Asset].[ElementAttribute] ON a.ElementID = ef.PrimaryReferencedElementID

INNER JOIN [AFDB.[Data].[Archive] ar ON (ar.ElementAttributeID = a.ID AND ar.Time between ef.StartTime and ef.EndTime)

INNER JOIN [AFDB].[EventFrame].[EventFrameTemplate] eft ON ef.EventFrameTemplateID = eft.ID

WHERE ef.StartTime BETWEEN '*-55w' AND '*-54w'

AND eft.Name = 'Phase'

Outcomes