Learn about specifying result columns in SELECT statements

The SELECT statements that you define to retrieve RDBMS data into PI tags must specify result columns for tag value and status. In addition, the result set can include columns for timestamp, questionable and annotations. There are two options for specifying the result columns in the SELECT statement: fixed position or aliased.

Fixed-position result columns

If you omit result column aliases, you must retrieve result columns in timestamp/value/status sequence. For example:

SELECT timestamp, value, status FROM table1 …

The timestamp column is optional. If you omit the timestamp, the value is written using the interface node system time. If the source table does not include a status column, you can specify a numeric value in the query (0 indicates good data):

SELECT timestamp, value, 0 FROM table1…

Aliased result columns

As an alternative to fixed positions, you can use aliases to assign result columns to tag attributes. Aliases must be specified in upper case, as shown. The following aliases are supported:

• PI_TAGNAME

• PI_TIMESTAMP

• PI_VALUE

• PI_STATUS

• PI_QUESTIONABLE

• PI_ANNOTATION

For example:

SELECT timestamp AS PI_TIMESTAMP, value AS PI_VALUE, status AS PI_STATUS, flag AS PI_QUESTIONABLE, annotation AS PI_ANNOTATION FROM table1 …

When you use aliases, the columns in the SELECT statement can be specified in any order – they are not required to be in the timestamp/value/status sequence required by the fixed position approach. When the columns in the SELECT list are aliased, the status column is not mandatory. If status is omitted, the interface writes a status of 0 (Good) to the tag. The same is true for the questionable and annotation columns; that is, if these columns are omitted the questionable bit is not set and the event is not annotated.

If the query retrieves multiple tag values from a single row, you can use numbered aliases to assign the results columns to target tags. The number of the alias corresponds to the value that you specify in the Location3 attribute of the target tag.

For details, see Update a predetermined set of tags (tag groups).