AnsweredAssumed Answered

PIRDBMS SQL Statement

Question asked by Dean.Bianchi on Feb 12, 2019
Latest reply on Feb 12, 2019 by Dean.Bianchi

I have a SQL table of alarms for some of our mobile fleet that I am trying to capture using a PI-RDBMS interface. We are looking to track durations as well as monitor when alarms have been active beyond a certain time limit as the existing application does not provide this information.

The interface is configured and working as I have been able to retrieve values for other telemetry successfully.

 

The table I am now working with is a little different in that it has 2 timestamp columns to work with - START_TIMESTAMP and END_TIMESTAMP

 

SQL.JPG

Each alarm will be written to its own Pi Tag, which will be identified by EQUIP_IDENT which is the vehicle and the ALARM_PROTOCOL_IDENT which identifies which alarm it is. For example in line 1 Pi Tag could be EX009.EngineOverspeedAlarm.

What I need to achieve is have the SQL statement write to the PI Tag: "ON" with the START_TIMESTAMP" and "OFF" with the END_TIMESTAMP.

 

I can do a single statement to write a single value, for example:

/SQL="SELECT START_TIMESTAMP, 'ON' AS PI_VALUE1,0 FROM ALARM_TRANS WHERE EQUIP_IDENT='EX002' AND ALARM_PROTOCOL_IDENT='10651' AND START_TIMESTAMP > ? ORDER BY START_TIMESTAMP ASC;" P1=TS

 

I am getting stuck on the syntax for the above for multiple values to the same tag (I am by no means an expert in SQL if that is not already obvious)

I have tried the following but it just keeps writing the last ON value only repetitively to PI with the same timestamp and does not write any OFF value:

 

/SQL="SELECT START_TIMESTAMP, 'ON' AS PI_VALUE1,0, END_TIMESTAMP, 'OFF' AS PI_VALUE2,0 FROM ALARM_TRANS WHERE EQUIP_IDENT='EX002' AND ALARM_PROTOCOL_IDENT='10651' AND START_TIMESTAMP > '01-DEC-2018' ORDER BY START_TIMESTAMP ASC;" P1=TS

 

I would appreciate any input to help get this working.

 

Thanks in advance

Outcomes