dgrigsby.fw

PI SQL Custom Table Valued Function

Discussion created by dgrigsby.fw on Apr 23, 2020
Latest reply on May 6, 2020 by dgrigsby.fw

I'm working with some relatively discrete laboratory analysis data that is being stored in PI at the same time for multiple attributes. The current GetSampledValues only allow for iteration over time ranges with timesteps which will then interpolate the values at those timesteps. I want to get the raw data entries for the times when the lab analysis was submitted. For this application it will work since like I mentioned all the tags have the same timestamp. However, there isn't a Template for GetCompressedValues and when I try to make one I get an error on my first Table Valued Function against the Master.Element.Archive.

 

My setup so far is this: 

Create Function Master.Element.TestArchive
(
@AttributeID GUID,
@StartTime DateTime,
@EndTime DateTime
)
AS
Select a.Timestamp, a.Value FROM Master.Element.Archive a
Where a.AttributeID = @AttributeID AND
a.TimeStamp between @StartTime and @EndTime

I am getting the error:

[SQL Query Engine] The table-valued function definition is not supported.

 

I can get it to execute successfully if I remove the @StartTime and @Endtime from the parameters section and replace them also in the query with '*-30d' and '*' respectively.

 

Why can't even define a @StartTime or @EndTime parameter in this context?

Outcomes