How to Point to a Particular Table Name in Historic Data

Using CDBPoint and CDBHistoric.
The CDBPoint tables has several columns that explains the time, date, description, name, and other information about the current state of the data. However, if looking at historic tables, it provides data related to enabled historic data without telling what the name of the point is. As a result, to point to a particular table name in historic data, you need to CROSS JOIN CDBPoint and CDBHistoric.

The Overview
This overview is meant to provide how to point to a particular table name in historic data.

This is an example:
-In ViewX, create a group and a point or use existing point (right-click design area and click on Edit Query to view  code).
-Click Default Mimic to display design area
-In Graphic, create a LIST and paste the code below:



CDBPoint
The SQL code:

SELECT TOP( 100000 )
"RecordId", "RecordTime" AS "~Time", "FormattedValue", "StateDesc", "QualityDesc", "ReasonDesc", "StatusDesc", "SuppressionTypeDesc", "Foreground", "Background", "Blink", "FileOffset" AS "_FileOffset"
FROM
CDBHISTORIC
WHERE
"Id" = 7051 AND "~Time" BETWEEN { OPC 'Hour - 23 Hours' } AND { OPC 'Hour - 23 Hours+1 Day' }
ORDER BY
"~Time" DESC, "_FileOffset" DESC



CROSS JOIN CDBPoint and CDBHistoric

You need to modify the code according to the column you would like to view from CDBPoint before you can generate the CDBHistoric table.

-CDBHistoric
-The SQL code:

SELECT TOP( 100000 )
H."RecordTime", P."Name", H."FormattedValue"
FROM
CDBPOINT AS P CROSS JOIN CDBHISTORIC AS H
WHERE
P.ID = 7051 AND H."RecordTime" BETWEEN { OPC 'Hour - 23 Hours' } AND { OPC 'Hour - 23 Hours+1 Day' } AND H.ID = P.ID

Опубликовано для: Schneider Electric Казахстан