You can use SQL Queries in your Logic programs to retrieve multiple raw historic values. As the values are retrieved via an SQL Query, the Logic program only retrieves one value at a time—multiple values are retrieved by using a 'loop' such as a WHERE or FOR clause. This causes the program to retrieve the historic values in the specified range one at a time, in turn.
To retrieve multiple historic values via SQL, you first need to define a TYPE. The Type defines a record set which includes the fields for which values are to be returned. The SQL query needs to be added to the VAR variables list, and it has to reference the record set.
Example:
In this example, an SQL Query is used in an ST Program. It allows the ST Program to retrieve a point's historic values between the start of the current hour (H in the OPC time standard) and the end of the current hour (H+1H in the OPC time standard).
TYPE
HistoricRecord : STRUCT
Time : DATE_AND_TIME;
ValueAsReal : LREAL;
END_STRUCT;
END_TYPE
PROGRAM NewStructuredTextProgram
VAR
Historic AT %S(SELECT RecordTime, ValueAsReal FROM CDBHistoric JOIN
CDBPoint USING (Id) WHERE CDBPoint.FullName = 'Random.Value' AND RecordTime
BETWEEN { OPC 'H' } AND { OPC 'H+1H' }) : RESULTSET OF HistoricRecord;
END_VAR;
END_PROGRAM
At the start of the program, there is a TYPE definition. This is used to create a record set. In this case, the record set is named HistoricRecord and it is a structured data type (STRUCT).
The values that are to be retrieved for the record set are specified in the TYPE definition: Time and Value. The data types for these values are also specified (DATE_AND_TIME and LREAL).
The VAR variables list contains the SQL Query that is used to define which point is to have its historic values read. It also defines the time range for the values—the program will only read the historic values for the period within the defined time range.
We will look at each part of the variable definition in turn:
Historic AT %S
Historic is the name of the variable that includes the SQL Query.
AT %S informs the program that the variable uses an SQL Query.
(SELECT RecordTime, ValueAsReal FROM CDBHistoric
This is the first part of the SQL Query. It instructs the program to retrieve the values from the RecordTime field and the ValueAsReal field in the CDBHistoric class. The RecordTime value will be used as the Time value (specified in the TYPE definition at the start of the program) and the ValueAsReal value will be used as the ValueAsReal value.
JOIN CDBPoint USING (Id)
As the CDBHistoric class does not contain the name of the required point, the SQL Query needs to define a JOIN. The JOIN combines the CDBPoint class and the CDBPoint class into a single table. To combine the point values and the historic values correctly for each point, the SQL Query has to define the criteria by which the program will join the two tables. The USING (Id) definition instructs the program to join the data in the CDBPoint class to the data in the CDBHistoric class based on the Id values—the Ids for the points are identical in both the CDBHistoric class and the CDBPoint class.
WHERE CDBPoint.FullName = 'Random.Value' AND RecordTime BETWEEN { OPC 'H' }
AND {OPC 'H+1H' })
The WHERE clause specifies the point for which historic data is to be retrieved. It also specifies the fields for which values are to be returned. The BETWEEN clause specifies the time range—the SQL Query only returns values that were stored during the specified time range.
: RESULTSET OF HistoricRecord;
Finally, the RESULTSET OF HistoricRecord instructs the program that the values that are returned are to be the values for the HistoricRecord record set.
You should construct your queries so that they query limited amounts of historic data. Large and inappropriate query searches can severely affect system performance and can potentially delay other system operations.