Sql variables Read query mapping examples

See the topic Supported Table Formats for more information on table formats.

Wide table format

Each data appears in a separate field. A typical Sql read query returns the following table.

field_Ts	field_Temperature	field_Humidity	field_Wind	…
11/01/2019 10h02	22.5	64	17	…

We want to map the variable @Temperature to the data in the field field_Temperature.

In the Row Selection tab, the row is selected as By position.

In the Mapping tab, only the Value is mapped. As the timestamp is not mapped, it is provided by PcVue.

Narrow table format

One field contains the values and other fields the context of the value. A typical Sql read query returns the following table.

field_Ts	field_Name	field_Value	field_Quality
11/01/2019 10h00	Wind	39	192
11/01/2019 10h03	Temperature	22,5	192
11/01/2019 10h05	Humidity	64	192

We want to map the variable @Temperature to the data in the field field_Name in the row that contains "Temperature".

In the Row Selection tab, the row is selected as by field value.

In the above example, this will use the data in row two. If we had chosen "Humidity" this would use the data in row three etc.

In the Mapping tab, the Value is mapped. As the timestamp is not mapped, it is provided by PcVue.

Wide table format

Each data variable appears in a separate field. The Sql read query has been configured to return several values for the same variable producing the following table.

field_Id	field_Ts	field_Temperature
1	2020/06/23 10:00:00	20
2	2020/06/23 11:00:00	22
3	2020/06/23 12:00:00	25

We want to map the variable @Temperature to the data in field field_Temperature. As the variable is trend recorded, we also want to use the timestamps in field field_Ts.

In the Row selection tab, the row is selected as All rows.

In the Mapping tab, both the Value and Timestamp are mapped.

Each time the query is executed, all rows are browsed and recorded. The above example would produce three lines in the archive unit.

20 with the timestamp 2020/06/23 10:00:00
22 with the timestamp 2020/06/23 11:00:00
25 with the timestamp 2020/06/23 11:00:00