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
FunctionalityDescription
WHERE ClausesDefines a condition on which to select the records or set of query parameters.
Result SortingSorts the returned records by one or more columns, ascending or descending (ORDERBY).
TOP PercentLimits the number of returned records to the specified top number, or percent of rows.
LIKE ExpressionsAllows you to query records using wild cards.
AS ConditionAllows you to give a different name for a column or table than what is stored in the database.
Eliminate Duplicate Return RowsEliminates duplicate records where all columns are equal (DISTINCT).
Group RecordsCombines records with identical values in the specified field list into a single record (GROUP BY).
SQL Aggregate FunctionsPerforms a calculation on a set of values and returns a single value (AVG, COUNT, MAX, MIN, SUM, STDEV, STDEVP, VAR, VARP).
Table JoinsCombines columns from two or more tables.
Quotation MarksSpecifies a string that contains a space, comma, or reserved word.
Timestamp FormatsSpecifies the types of supported timestamps.
Date and Time ShortcutsLists the date shortcuts that you can use in a SQL statement.
Comparison OperatorsDescribes the use of comparison symbols within SQL statements.
Logical OperatorsAllows queries to be developed using Boolean logic (OR, AND, NOT).
Parenthetical ExpressionsControls 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.