Update points from more rows and columns (RxC row by column distribution using aliases) with SELECT list columns renamed
- Last UpdatedNov 18, 2022
- 2 minute read
- PI System
- Interfaces
Update points from more rows and columns (RxC row by column distribution using aliases) with SELECT list columns renamed
In this approach, each row contains the timestamped sample data for temperature, pressure and level. The individual rows are distributed according to the corresponding alias column. The distributor tag executes the query that retrieves the data and updates the target points. To indicate that the tag is a distributor tag, set Location3 to -2.
SELECT timestamp AS PI_TIMESTAMP,
aliasTemp AS PI_TAGNAME1, temperature AS PI_VALUE1, 0 AS PI_STATUS1,
aliasPress AS PI_TAGNAME2, pressure AS PI_VALUE2, 0 AS PI_STATUS2,
aliasLev AS PI_TAGNAME3, level AS PI_VALUE3, 0 AS PI_STATUS3
FROM table8
WHERE timestamp > ? AND key1 LIKE 'Tank1' ORDER BY timestamp ASC;
Configure the target PI point with the following settings.
|
Point Attribute |
Description |
Setting |
|---|---|---|
|
ExDesc |
Placeholders and options |
Distributor point: P1= ' Tank1_Temperature1 ' /TS Target points: /ALIAS=T1_Temp1 /ALIAS=T1_Press1 /ALIAS=T1_Lev1 /ALIAS=T1_Temp2 /ALIAS=T1_Press2 /ALIAS=T1_Lev2 |
|
Location2 |
First row/all rows |
N/A for RxC |
|
Location3 |
Data distribution strategy |
RxC Distributor point: -2 Target points: 0 |
|
Location4 |
Scan class |
1 |
|
Location5 |
Exception reporting |
0 (Exception reporting is enabled) |
|
InstrumentTag |
Name of the SQL file (Distributor point only) |
Distributor point: Example8.SQL |
|
PointType |
Data type of PI points |
Float32 |
In the RDBMS, create a table formatted as follows.
|
Column name |
Column data type |
|
|---|---|---|
|
SQL Server data type |
Microsoft Access data type |
|
|
timestamp |
DateTime |
Date/Time |
|
temperature |
Real |
Number-Single Precision |
|
pressure |
Real |
Number-Single Precision |
|
level |
Real |
Number-Single Precision |
|
aliasTemp, aliasPress, aliasLev, key1 |
Varchar(255) |
Text(255) |
Example result set.
|
PI_TIMESTAMP |
PI_ TAGNAME1 |
PI_ VALUE1 |
PI_ STATUS1 |
PI_ TAGNAME2 |
PI_ VALUE2 |
PI_ STATUS2 |
PI_ TAGNAME3 |
PI_ VALUE3 |
PI_ STATUS3 |
|---|---|---|---|---|---|---|---|---|---|
|
20-Oct-2015 08:15:00 |
T1_Temp1 |
42 |
0 |
T1_Press1 |
20 |
0 |
T1_Lev1 |
303 |
0 |
|
20-Oct-2015 08:30:00 |
T1_Temp2 |
43 |
0 |
T1_Press2 |
22 |
0 |
T1_Lev2 |
305 |
0 |
Given this result set, the interface assigns values to PI points as follows.
|
Point |
Timestamp |
Value assigned |
|---|---|---|
|
Tank1_Temperature1 |
20-Oct-2015 08:15:00 |
42 |
|
Tank1_Pressure1 |
20-Oct-2015 08:15:00 |
20 |
|
Tank1_Level1 |
20-Oct-2015 08:15:00 |
303 |
|
Tank1_Temperature2 |
20-Oct-2015 08:30:00 |
43 |
|
Tank1_Pressure2 |
20-Oct-2015 08:30:00 |
22 |
|
Tank1_Level2 |
20-Oct-2015 08:30:00 |
305 |