Update a predetermined set of tags (tag groups)

To update a predetermined set of tags using a single query, set the tags' InstrumentTag attribute to the path and name of the file that contains the query. This approach is referred to as a tag group. All target tags in the group refer to the same query file and receive the same timestamp when data is returned to them.

When defining a tag group, you configure a primary tag; that is, a tag, which executes the query, as follows:

• Location2: To process only the first row returned by the query, set Location2 to 0. To process all rows returned by the query, set Location2 to 1.

• Location3: Set to 2 if the first column returned by the query is a timestamp; otherwise set to 1.

  Note: If the first column returned by the query is a timestamp, the interface assumes that the column is the optional timestamp used to timestamp all tags and will use 2 as the index for the primary tag, even if set to 1. This can cause issues when attempting to update tags defined as timestamp type on the PI Data Archive where the first column returned is meant to be the value of a timestamp tag. To avoid this issue, use column aliases or change the order of the query to return a non-timestamp column as the first column.

• Placeholders: In the ExDesc field of the primary tag, define the placeholders required by the query.

To specify the value to be assigned to the tag, set each tag's Location3 attribute to the ordinal number of the column returned by the query. For example, define a query that returns the following columns:

To update Tag1 with the first value and status and Tag2 with the second value and status, configure the tags as follows:

1. Define a text file containing the query; for example:

   SELECT timestamp, value1, status1, value2, status2 FROM table1
   WHERE timestamp > ? ORDER BY timestamp ASC;

2. In the query, each value column must be followed by its associated status. If the RDBMS table does not contain status columns, you can specify the status as a literal ( SELECT timestamp, value1, 0 as status1… ).

3. In each target tag, set its attributes as follows:

   • InstrumentTag : Path and name of query file. (Must be the same in all tags in the group.)

   • Location3: Column number from which value is to be read. (Specify 2 for Tag1, and 4 for Tag2.)

   • ExDesc : In the ExDesc attribute of the tag with the lowest Location3, define the placeholders used in the query (e.g. P1=TS)

You can also map result columns to tags by specifying aliases in the query, which enables you to order the result columns without maintaining a strict positional correspondence with the value you specify in Location3. The aliases end in a numeric value that corresponds to the value you specify in Location3 , but the result column can appear in any position in the query. You can specify the following aliases (where n indicates the numeric value in Location3.

• PI_VALUEn

• PI_STATUSn

• PI_QUESTIONABLEn

• PI_ANNOTATIONn

• PI_TIMESTAMPn (use when each value in the result table has its own timestamp column)

For example, in the following query, VALUE2 appears before VALUE1. Aliases are used to ensure that each is assigned to the correct PI tag:

SELECT timestamp AS PI_TIMESTAMP, value2 AS PI_VALUE2, value1 AS PI_VALUE1, 0 AS
PI_STATUS1, 0 AS PI_STATUS2
FROM table1 WHERE timestamp > ? ORDER BY timestamp ASC;