I am trying to read values from a SQL database using the RDBMS interface and use these values to create batches inside PI using the Batch Generator (BaGen). Unfortunately, I am running into an issue with the way the batch start and stop times are stored inside the database.
From the RDBMS interface manual, I understand that a tag’s exdesc can contain multiple SQL statements, but only one SELECT statement. Is this correct? The reason I ask is that I am trying to use a RDBMS tag as an active point for BaGen. As such, I need the point to have transitions (ideally, from 0->1 and 1->0 to indicate the start and stop of UnitBatches, respectively). The database table I am pulling the batch status from only contains one row per batch. This row contains the start time and stop time for the batch. When a batch is started, a row is added to the table with the start time populated and end time as NULL. When the batch is finished, the stop time in the row is populated as appropriate. I have attached a screenshot of the table for reference which shows examples of both of these scenarios.
I wrote and successfully tested the below query to accommodate for this processing sequence. A “1” is written to the PI tag when a row for a batch is added to the table and a “0” when the end time in the row is updated (i.e., the batch has ended). Unfortunately, when recovering data over an extended period of time as I need to do, any batches that have already completed do not follow this processing sequence – they already have both a start and stop time. My thought was to add a second SELECT statement to the exdesc of the tag. This second statement would account for cases when both the start and stop times are populated, but going back to my original question, I do not think this is possible.
Can you think of another way to go about this? Is there perhaps a way to process the same table row multiple times (if so, we could add logic to output a “1” during the first iteration and “0” during the second iteration for completed batches)? If that isn’t possible, I thought about creating two RDBMS tags – one reads batch starts, the other batch stops – and creating a PE tag which is triggered by both, but I don’t think PE tags can have more than one trigger, so we would have to go clock-based and that seems like a less-than-ideal approach.
/SQL=” SELECT CASE WHEN [Stopdate] is null THEN DATEADD(day, DATEDIFF(day,'18991230',[Startdate]), CAST([Starttime] AS DATETIME2(3))) ELSE DATEADD(day, DATEDIFF(day,'18991230',[Stopdate]), CAST([Stoptime] AS DATETIME2(3))) END as PI_TIMESTAMP, CASE WHEN [Stopdate] is null THEN 0 ELSE 1 END as PI_VALUE, 0 FROM [C4].[dbo].[Batchindex] WHERE DATEADD(day, DATEDIFF(day,'18991230',[Startdate]), CAST([Starttime] AS DATETIME2(3)))>? OR DATEADD(day, DATEDIFF(day,'18991230',[Stopdate]), CAST([Stoptime] AS DATETIME2(3)))>? ORDER BY PI_TIMESTAMP ASC;" P1=TS P2=P1
I appreciate any thoughts you could share.