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 time stamp is extracted, if you want to retrieve a time stamp with each data value. Enter a value of zero if you have no requirement for a time stamp to be retrieved with each data value. You should also enter a value of zero if the other database does not support time stamp information.
NOTE: 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 time stamp is extracted.
- 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 time stamp. 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 time stamps 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 ClearSCADA 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.