Supported Table Formats
The following table formats are supported when using Sql Read Queries as a source for the value of variables (SELECT).
They are also supported when using Sql write queries to affect one or more rows (INSERT, UPDATE...)
A table can be the result of one or more joins or any other processing performed at the data source level.
The maximum number of values that it is possible to read is limited by the maximum column count supported by ADO.Net provider, or by the Data Source minus 1.
Narrow table format
The narrow format is a table with one field containing the values and another the metadata of the value. This is one of the most common formats and is used in PcVue by the HDS for log and trend data.
| field_Ts | field_Name | field_Value | field_Quality |
| 11/01/2019 10h00 | Temperature | 22,5 | 192 |
| 11/01/2019 10h00 | Humidity | 63 | 192 |
| 11/01/2019 10h00 | Wind | 39 | 192 |
| 11/01/2019 10h01 | Temperature | 22,5 | 192 |
| 11/01/2019 10h01 | Humidity | 64 | 192 |
| 11/01/2019 10h01 | Wind | 12 | 192 |
| 11/01/2019 10h02 | Humidity | 64 | 192 |
| 11/01/2019 10h02 | Humidity | 64 | 192 |
The simplest query for this format is to read the last value of each metric (temperature, humidity and wind), but this requires one Sql request for each variable and is not very efficient.
|
SELECT TOP(1) [field_Ts], [field_Value], [field_Name] FROM [NarrowTable] WHERE [field_Name] = ‘Temperature’ ORDER BY [field_Ts] DESC |
An alternative approach is to use query mutualization obtaining the last value of each metric, and then mapping the result to several variables.
The example below is given for Sql Server and may not work with other RDBMS.
|
;WITH CTE AS ( SELECT [field_Ts], [field_Value], [field_Name], ROW_NUMBER() OVER(PARTITION BY [field_Name] ORDER BY [field_Ts] DESC) AS "RowNumber" FROM [NarrowTable] WHERE [field_Ts] > DATEADD(DD, -10, GETDATE()) ) SELECT [field_Ts], [field_Value], [field_Name] FROM CTE WHERE RowNumber=1 ORDER BY [field_Ts] DESC |
Using the above sample data the result of this query is as follows.
| 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 |
In this example, 3 values and their quality are read with one SQL Query execution, but it could be extended to many more.
Wide table format with single timestamp
The wide table format is that where each metric appears in a separate field. This format is a snapshot of several metrics at a particular time.
| field_Ts | field_Temperature | field_Humidity | field_Wind | … |
| 11/01/2019 10h00 | 22,5 | 63 | 39 | … |
| 11/01/2019 10h01 | 22,5 | 64 | 12 | … |
| 11/01/2019 10h02 | 22,5 | 64 | 17 | … |
This is the easier format to use if we are interested in only keeping the latest value of each metric (Temperature, Humidity and Wind).
|
SELECT TOP(1) [field_Ts], [field_Temperature], [field_Humidity] [field_Wind] FROM [WideTable] ORDER BY [field_Ts] DESC |
Using the above sample data the result of this query is as follows.
| field_Ts | field_Temperature | field_Humidity | field_Wind | … |
| 11/01/2019 10h02 | 22,5 | 64 | 17 | … |
In this example, 3 values are read with one Sql Query but it could be extended to many more.
Wide table format with multiple timestamps
Similar to the above but with the timestamp repeated for each metric. This is a format in common use by SCADA providers.
| field_Ts | field_Ts Temperature |
field_ValueTemperature |
field_TsHumidity |
Field_ValueHumidity |
… |
| 11/01/2019 10h00 | 11/01/2019 10h00 | 22,5 | 11/01/2019 10h00 | 63 | … |
| 11/01/2019 10h01 | 11/01/2019 10h01 | 23,0 | 11/01/2019 10h01 | 64 | … |
| 11/01/2019 10h02 | 11/01/2019 10h02 | 23,5 | 11/01/2019 10h01 | 64 | … |
This an easy format to use if you are interested in only keeping the latest value of each metric (Temperature, Humidity and Wind).
|
SELECT TOP(1) [field_Tstemperature], [field_ValueTemperature], [field_TsHumidity] [field_ValueHumidity] FROM [WideTable] ORDER BY [field_Ts] DESC |
Using the above sample data the result of this query is as follows.
| field_TsTemperature | field_ValueTemperature | field_TsHumidity | field_ValueHumidity | … |
| 11/01/2019 10h02 | 23,5 | 11/01/2019 10h02 | 64 | … |
With this example, 2 or more values are read with one Sql Query but it could be extended to many more.
Wide table format without timestamp
| field_BatchID | field_Failed | field_Score | field_Operator |
| 4012 | 0 | 965 | AD |
| 4013 | 0 | 1093 | AD |
| 4014 | 0 | 875 | AD |
| 4015 | 1 | 15 | AD |
Again, easy to use if you are interested in only keeping only the n latest values for all the metrics (Failed, Score, Operator).
|
SELECT TOP(2) [field_Failed], [field_Score], [field_Operator] FROM [WideTable] ORDER BY [field_BatchID] DESC |
Using the above sample data the result of this query is as follows.
| field_BatchID | field_Failed | field_Score | field_Operator |
| 4014 | 0 | 875 | AD |
| 4015 | 1 | 15 | AD |
With this example, it is possible to read one or more value sets with one Sql Query.
Fixed table format
A table in which the number of rows and their use is fixed.
| field_Day | field_Production |
| Monday | 10005 |
| Tuesday | 98509 |
| Wednesday | 99678 |
| Thursday | 10014 |
| Friday | 99256 |
| Saturday | 73968 |
| Sunday | NULL |
It is not necessary to filter or order the result.
|
SELECT [field_Day], [field_Production], [field_Operator] FROM [FixTable] |
Using the above sample data the result of this query is as follows.
| field_Day | field_Production |
| Monday | 10005 |
| Tuesday | 98509 |
| Wednesday | 99678 |
| Thursday | 10014 |
| Friday | 99256 |
| Saturday | 73968 |
| Sunday | NULL |
With this example, it is possible to read one or more value sets with one Sql Query.
Alternatively, the result could be just one scalar value.
|
SELECT COUNT(*) as Result FROM [FixTable] |
Using the above sample data the result of this query is as follows.
| Result |
| 7 |