AnsweredAssumed Answered

Location2 = 1 and Location5=0, issue still archiving duplicates PI for RDBMS

Question asked by W@d3 on Oct 8, 2018
Latest reply on Oct 10, 2018 by W@d3



     I have an SQL database.


     In this database, the vendors machine will INSERT multiple rows into the sql table at a single time. Hence, the time stamp is the InsertTime and will be equal to the same value for multiple rows in the sql data table that my queries are querying. 


     I use InsertTime as my Timestamp.


     I also use InsertTime to know what group of rows was inserted together.


     I want to make sure I capture every single updated row.


     My SQL query looks kind of like the following:


     /SQL="SELECT [ValueToBeRecord] AS PI_VALUE, [InsertTime] AS PI_TIMESTAMP, 0 AS PI_STATUS FROM [SupervisorDB].[dbo].[View_Liner_S3_tiles]  WHERE [InsertTime] = (SELECT MAX(InsertTime) FROM [SupervisorDB].[dbo].[View_Liner_S3_tiles]) Order By [Timestamp] asc,[ID_tile] asc;"


     This query gets me the most recent group of inserted rows from my table pretty well and in the order that I desire.


     (about 15 rows as a time, it gets, based on the limited speed of the production machine.)



     I have Location2=1, so my query will select multiple rows at a time.


     I have Location5=0, so it should not archive the incoming event if the archive already has a value with the same timestamp? (In my case, I use InsertTime as my timestamp)


     However, despite location5 being equal to 0, it will archive the same group of rows from the sql data table as it did in the previous scan.


     Consequently, I end up with A LOT of duplicate values, especially when the production machine is not running.


     Can anybody help me? What am I missing?