Using retrieval options in a Transact-SQL statement

You can retrieve data in the AVEVA Historian extension tables using normal Transact-SQL code, as well as the specialized SQL time domain extensions provided by the AVEVA Historian. The AVEVA Historian extensions provide an easy way to query time-based data from the history tables. They also provide additional functionality not supported by Transact-SQL.

Note: The wwParameters extension is reserved for future use. The wwRowCount parameter is still supported, but is deprecated in favor of wwCycleCount.

The extensions are implemented as "virtual" columns in the extension tables. When you query an extension table, you can specify values for these column parameters to manipulate the data that will be returned. You will need to specify any real-time extension parameters each time that you execute the query.

For example, you could specify a value for the wwResolution column in the query so that a resolution is applied to the returned data set:

SELECT DateTime, Value

FROM History

WHERE TagName = 'SysTimeSec'

AND DateTime >= '2001-12-02 10:00:00'

AND DateTime <= '2001-12-02 10:02:00'

AND Value >= 50

AND wwResolution = 10

AND wwRetrievalMode = 'cyclic'

Because the extension tables provide additional functionality that is not possible in a normal SQL Server, certain limitations apply to the Transact-SQL supported by these tables. For more information, see Unsupported or limited syntax options.

Although the Microsoft SQL Server may be configured to be case-sensitive, the values for the virtual columns in the extension tables are always case-insensitive.

Note: You cannot use the IN clause or OR clause to specify more than one condition for a time domain extension. For example, "wwVersion IN ('original', 'latest')" and "wwRetrievalMode = 'Delta' OR wwVersion = 'latest'" are not supported.

For general information on creating SQL queries, see your Microsoft SQL Server documentation.