Use the fields within the Column Match Lookup section of the ODBC tab to specify the requirements for extracting data by reference to a specific value, such as a point name or ID. The fields are available for use when you specify a Value Lookup Method (see Configure SQL Query Lookup Properties).
- Lookup Column—Specify the column index that identifies the point’s location within the query’s results table. The column index has to be in the range specified in the SQL Query (for which the point is to extract data).
- Lookup Value—Specify the value within the Lookup Column, that indicates the row within which the point data is located. The Lookup Value may be, for example, the point name or ID.
- Value Column—Specify the column from which the point’s value is extracted.
- Timestamp Column—Specify the column from which the point’s timestamp is extracted, if you want to retrieve a timestamp with each data value. The timestamps should be in UTC.
Enter a value of zero if you have no requirement for a timestamp to be retrieved with each data value. You should also enter a value of zero if the other database does not support timestamp information.
If the source database includes historic data, you can set a point to be updated with all matching values from that database. To do this, you need to use the Timestamp Column field to specify the column from which the point’s timestamp is extracted.
If you specify a Timestamp Column value of zero, Geo SCADA Expert will update the Geo SCADA Expert point with the retrieved data value each time it executes a query. If you specify a Timestamp Column value other than zero, Geo SCADA Expert will only update the Geo SCADA Expert point's value when the retrieved data value's timestamp changes. Therefore, you should ensure that the timestamp correctly reflects the time the retrieved data value changes.
- Quality Column—Specify the column from which the point’s quality is extracted, if you want to retrieve the quality of each data value. Enter a value of zero if you have no requirement for quality details to be retrieved with each data value. You should also enter a value of zero if the other database does not support quality information.
- Use All Results—If the source database includes historic data, select this check box to update a point with all matching values, in order of timestamp. If you select the Use All Results check box, you also need to define the Timestamp Column (above). The Timestamp Column setting allows you to specify the location of the timestamps in the query’s results table.
Clear the Use All Results check box if the source database includes only current data, or if you want to update a point with just the latest matching value. The point value only updates in the Geo SCADA Expert database whenever it differs from the previously retrieved value.
For an example of how a point might be configured to extract data from an ODBC Query results table using the Value Lookup Method, see Query an Oracle Database using the Value Lookup Method.