Use a criteria condition on a column of variant data

The AVEVA Historian OLE DB provider sends variant data to the SQL Server as a string. If the query contains a criteria condition on a column containing variant type data, the filtering is handled by SQL Server. An example of a criteria condition is:

WHERE ... vValue = 2

To perform the filtering, the SQL Server must determine the data type of the constant (in this example, 2), and attempt to convert the variant (string) to this destination type. The SQL Server assumes that a constant without a decimal is an integer, and attempts to convert the string to an integer type. This conversion will fail in SQL Server if the string actually represents a float (for example, 2.00123).

You should explicitly state the destination type by means of a CONVERT function. This is the only reliable way of filtering on the vValue column, which contains variant data.

For example:

SELECT DateTime, Quality, OPCQuality, QualityDetail, Value, vValue, TagName

FROM History

WHERE TagName IN ('ADxxxF36', 'SysTimeMin', 'SysPulse')

AND DateTime >= '12-04-2001 04:00:00.000'

AND DateTime <= '12-04-2001 04:03:00.000'

AND wwRetrievalMode = 'Delta'

AND convert(float, vValue) = 2

The following is another example:

SELECT DateTime, Quality, OPCQuality, QualityDetail, Value, vValue, TagName

FROM History

WHERE TagName IN ('VectorX', 'SysTimeMin', 'SysPulse')

AND DateTime >= '20020313 04:00:07.000'

AND DateTime <= '20020313 04:01:00.000'

AND wwRetrievalMode = 'Delta'

AND convert(float, vValue) > 1

AND convert(float, vValue) < 2