Imre.Csonka

PI SQL/before, before only

Discussion created by Imre.Csonka on Mar 30, 2020

Hi guys,

 

I use PI on SQL server and want to mimic  before, before only access mode. I used below query which usually works, but sometime cause exception. That happens when no return value. I would expect using EXISTS clause avoids this problem bot doesn't. Can anyone help?

@pDATE, @pTAG_NAME, @pBACK_DAYS are the input parameters and I think they are self-explanatory:

 

set @minDATE =DATEADD(d,-@pBACK_DAYS,@pDATE)
if EXISTS(SELECT [value] FROM [PIARCHIVE].[piarchive]..[picomp2]
where Tag=@pTAG_NAME AND [time]<= @pDATE and [time]>@minDATE)
BEGIN
SELECT @vTIMESTAMP=MAX([time]) FROM [PIARCHIVE].[piarchive]..[picomp2]
where Tag=@pTAG_NAME AND [time]<= @pDATE and [time]>@minDATE

select @vVALUE=[value]
FROM [PIARCHIVE].[piarchive]..[picomp2]
where Tag=@pTAG_NAME AND [time]=@vTIMESTAMP
select @vUOM=[engunits] FROM [PIARCHIVE].[pipoint]..[pipoint]
where Tag=@pTAG_NAME
SET @vSUCCESS='TRUE'
END ELSE SET @vSUCCESS='FALSE'

Outcomes