Update from multi-value rows containing name of target points (RxC)

To handle result rows that contain data that must be written to several different tags, use the RxC ("row by column") approach, which is a combination of the tag group and distribution strategies. Following is a basic example of a multi-value row, containing one timestamp followed by a series of tag name/value/status sets for each target tag:

Timestamp,Name1,Value1,Status1,Name2,Value2,Status2,…

Multi-value rows might contain a different timestamp for each target tag; for example:

Timestamp1,Name1,Value1,Status1,Timestamp2,Name2,Value2,Status2,…

To distribute the data from such rows to the target tags, you define an RxC distributor tag, setting its Location3 attribute to -2 , and define the required SQL query using positional aliases for result columns ( PI_TIMESTAMPn , PI_TAGNAMEn , PI_VALUEn , PI_STATUSn , PI_QUESTIONABLEn, PI_ANNOTATIONn ). If the result row contains only one timestamp, use PI_TIMESTAMP. Distributor and target tags must be in the same scan class unless you specify an alias (using the /ALIAS parameters).

After the query is executed, the interface sends two events to the distributor tag:

• The number of events successfully distributed to target tags

• The number of rows in the result set

Both entries are timestamped with the current time; that is, both events appear in the archive with the same timestamp.

The following example query returns a result row that can be distributed to target tags that track level, temperature and density for individual tanks:

SELECT timestamp AS PI_TIMESTAMP1, 'tag1' AS PI_TAGNAME1, [level] AS PI_VALUE1,
0 as PI_STATUS1,
sampletime AS PI_TIMESTAMP2, 'tag2' AS PI_TAGNAME2, temperature AS PI_VALUE2,
temperature_status AS PI_STATUS2,
sampletime AS PI_TIMESTAMP3,'tag3' AS PI_TAGNAME3, density AS PI_VALUE3,
density_status AS PI_STATUS3
FROM table1
WHERE timestamp > ? AND key1 = 'Tank1';

Note: To configure the interface to ignore NULLs for the tag group and RxC strategies, go to the PI ICU rdbodbc > Optional Parameters tab and check Ignore Nulls.