Supported SELECT Statement Syntax

SELECT statements allow you to retrieve data from the Historian database for reporting and analysis. The SELECT statements that the Historian OLE DB provider supports follow standard SQL-92 conventions. You can use SELECT statements to retrieve information from any of the columns in any of the Historian tables. The SELECT statement returns a snapshot of data at the given time of the query.

The order that you specify the columns in the SELECT statement controls how the data is returned. For more information on the tables and each of the columns in each table, refer to Historian Database Tables .

The following table outlines the supported SQL functions that you can use in a SELECT statement to access information in the Historian database tables.

Table 1. Supported SELECT Statement Functionality
Functionality	Description
WHERE Clauses	Defines a condition on which to select the records or set of query parameters.
Result Sorting	Sorts the returned records by one or more columns, ascending or descending (`ORDERBY`).
TOP Percent	Limits the number of returned records to the specified top number, or percent of rows.
LIKE Expressions	Allows you to query records using wild cards.
AS Condition	Allows you to give a different name for a column or table than what is stored in the database.
Eliminate Duplicate Return Rows	Eliminates duplicate records where all columns are equal (`DISTINCT`).
Group Records	Combines records with identical values in the specified field list into a single record (`GROUP BY`).
SQL Aggregate Functions	Performs a calculation on a set of values and returns a single value (`AVG`, `COUNT`, `MAX`, `MIN`, `SUM`, `STDEV`, `STDEVP`, `VAR`, `VARP`).
Table Joins	Combines columns from two or more tables.
Quotation Marks	Specifies a string that contains a space, comma, or reserved word.
Timestamp Formats	Specifies the types of supported timestamps.
Date and Time Shortcuts	Lists the date shortcuts that you can use in a SQL statement.
Comparison Operators	Describes the use of comparison symbols within SQL statements.
Logical Operators	Allows queries to be developed using Boolean logic (`OR`, `AND`, `NOT`).
Parenthetical Expressions	Controls the order of evaluation of the logical operators in an expression.

The sections that follow describe the supported SELECT statement functions. These sections also describe the appropriate syntax within these functions, including the use of wildcards, quotation marks, dates, comparison symbols, and parentheses.

Note: In order to query tag names with spaces in them you must enclose the full tag name in double quotes. For example, to query the Copy of 5vkn391s.Simulation00001 tag from the ihTrend table, you would write the following query: SELECT "Copy of 5vkn391s.Simulation00001" from ihTrend. Alternately, you can remove the spaces from the tag name.