1 Reply Latest reply on Feb 20, 2019 1:59 PM by Dan Fishman

    PI OLEDB

    jrheeders

      Hi,

       

      I want to write Historian data into MSSQL within hour buckets that calculates the average value.

       

      If I use this code it work perfect:

      Select Tag,Avg(Cast(Value as float)) as Value  FRom FTHLINK.piarchive..picomp2

      WHERE

      tag = 'Plant.Kao0:RSLinx Enterprise:Kao0.ANALOG_INPUTS117..ANA_IN_EU'

      and time >= '2016-01-01 00:00:00' and

      time < '2016-01-01 01:00:00'

      Group By Tag

       

      But if I use the Openquery which is recommended I am getting an error the whole time complaining about the float conversion:

       

      Declare @strQuery Nvarchar(Max)

      Declare @TagName Varchar(250)

      Declare @StartTime Varchar(250)

      Declare @EndTime Varchar(250)

       

      Set @TagNAme = 'Plant.Kao0:RSLinx Enterprise:Kao0.ANALOG_INPUTS117..ANA_IN_EU'

      Set @StartTime = '2016-01-01 00:00:00'

      Set @EndTime = '2016-01-01 01:00:00'

      Set @strQuery = 'Select * From OPENQUERY (FTHLINK, ''SELECT Tag, '''''+@StartTime+''''' as TimeStamp, Avg(Cast(Value as Float)) as Value FROM piarchive..picomp2 WHERE tag = '''''+ @TagName + ''''' AND time >= '''''+@StartTime+''''' AND time < ''''' +@EndTime+ ''''' Group by Tag'' )'

      EXEC sp_executesql @strQuery

       

      Any help would be appreciated.