Supported SQL Syntax

The Historian OLE DB Provider supports the use of WHERE clauses to specify search conditions in a SELECT statement. You can specify a condition for any column in the table using the WHERE clause.

For example, you could use a simple WHERE clause to search all rows of data in the ihTags table, where the DataType column equals SingleFloat. In another instance, you might want to find all tags that belong to a particular collector. Or, you might also want to search for all tags with a certain poll rate, or range of poll rates, or ones with polling disabled.

For more information on the columns for each individual Historian table, see Historian Database Tables.

Example 1: Search for All Single Float Tags

SELECT* FROM ihtags WHERE datatype=singlefloat

Example 2: Specify Query Parameters to Obtain String Data

In the following example, you change the SamplingMode column from the default value of Calculated to Interpolated in order to retrieve string data.

SELECT* FROM ihrawdata WHERE tagname=SimulationString00001
AND samplingmode=interpolated
AND IntervalMilliseconds=1H

Example 3: Use a WHERE Clause to Specify a Time Range

SELECT* FROM ihmessages WHERE timestamp>bom

Example 4: Use a Complex WHERE Clause to Find All Tags With a Specific Name and Description Pattern

SELECT* FROM ihtags
WHERE(tagname LIKE '*001*' AND description LIKE '*sim*')
OR (tagname LIKE '*02*'
AND (description LIKE '*sec*' OR description LIKE '*sim*'))
AND (timestamptype=source OR timestamptype=collector)

For more information on building complex WHERE clauses, see Logical Operators and Parenthetical Expressions.

The Historian OLE DB Provider supports the use of ORDER BY in a SELECT statement. If you do not specify ORDER BY, the output of the row order cannot be assumed. For example, if you want to order the rows returned from the ihCollectors table by the CollectorName column, you would need to include that column name in ORDER BY. As a more common example, when requesting timestamps with data, you should use the Timestamp column with ORDER BY to ensure that the samples are sorted in order by time.

ORDER BY sorts the returned records by one or more specified columns in either ascending or descending order. If you do not specify ascending or descending, ORDER BY uses ascending order. You can order results by one or more columns. If you sort by multiple columns, the sorting priority begins with the first column listed in the query, and then the next column, and so on.

If you specify ascending or descending order with ORDER BY, use the abbreviations in the following table or spell out the whole word.

The Historian OLE DB Provider treats Null values as the lowest possible values. It processes ORDER BY before it performs any RowCount truncation.

The following examples display simple and more complex examples of ORDER BY in actual SQL statements.

Example 1: Retrieve Collectors in Descending Order Sorted by CollectorName Column

SELECT * FROM ihcollectors ORDER BY collectorname DESC

Example 2: Retrieve Messages in Ascending Order Sorted by Time and Other Columns

SELECT * FROM ihmessages
WHERE timestamp>='5-oct-2001 00:00:00' 
AND timestamp<='18-jan-2002 00:00:00'
ORDER BY timestamp, topic, username, messagenumber, messagestring

The Historian OLE DB Provider supports the use of the TOP predicate in a SELECT statement. With the TOP predicate, you can limit the number of rows returned to a specified number or percentage of rows. After you indicate the number of rows or percentage of rows with the TOP predicate, enter the rest of the query. Typically, you include ORDER BY in the query to sort the rows in a specified order.

When you SELECT the top number or top percentage of rows, the returned value is limited by the RowCount. For instance, say you want the top 30 percent of rows from a query that can return a possible 10,000 rows, but the RowCount is set to 1000. The percentage logic processes the 3000 rows first, then it reduces the number to 1000 rows, as specified by RowCount. The final result returns 1000 rows, even though the top 30 percent is processed first. Use a SET statement or WHERE clause to change or disable the RowCount behavior.

The following examples display how to return the top 40 rows in the ihTags table and the top 10 and top 10 percent of rows from the ihMessages table.

Example 1: Return the Top 40 Tags in Alphabetical Order

SELECT TOP 40 * FROM ihtags ORDER BY Tagname

Example 2: Return the Top 10 Most Recent Messages

SELECT TOP 10 timestamp, topic, username, messagestring FROM ihmessages WHERE timestamp<Now ORDER BY timestamp DESC

Example 3: Return the Top 10 Percent, RowCount Disabled

SET rowcount=0
SELECT TOP 10 PERCENT timestamp, topic, username, messagestring
FROM ihmessages WHERE timestamp<Now
ORDER BY timestamp DESC

The Historian OLE DB Provider supports the use of the LIKE expression. Use the LIKE expression when searching for column data similar to a specified text string. By using wildcards, you can specify the text strings that you want to search. You can use the wildcard before, after, or before and after the text that you want to search for. The * symbol represents multiple unknown characters in a search string. The ? wildcard represents a single unknown character.

The first example displays how to use * wildcards to select all tags that contain a specific string in the tag name. The second example displays how to use a ? wildcard to select all tags when you are only unsure of one character in the tag name.

Example 1: Use LIKE With Multiple Character Replacement

SELECT * FROM ihtags WHERE tagname LIKE *.Simulation*
ORDER BY tagname
SELECT * FROM ihtags WHERE tagname LIKE %.Simulation%

Example 2: Use LIKE With Single Character Replacement

SELECT * FROM ihtags WHERE tagname LIKE MYSERVER.Simulation0000?
ORDER BY tagname
SELECT * FROM ihtags WHERE tagname LIKE MYSERVER.Simulation0000'_'
ORDER BY tagname

Use AS when you want to control the name of an output column. You can use AS in all columns and tables except the ihTrend table. In the ihTrend table, you can only use AS with the TimeStamp column.

Example: Set the Output Column Name

SELECT status, collectorname AS Name, collectortype, status AS 'The Status', collectordescription FROM ihcollectors

The Historian OLE DB Provider supports the use of DISTINCT in a SELECT statement. DISTINCT eliminates duplicate rows when all columns are equal. Floating-point values, however, may not compare as expected, depending on the precision. For example, if the numbers to the right of the decimal point are not equal for all values, similar columns are not eliminated. The columns must be exactly equal to be eliminated.

Example 1: Retrieve the Set of Unique Data Types Used in an Archive

SELECT DISTINCT datatype FROM ihtags

Example 2: Retrieve the Set of Tags With Raw Data Samples on a Specific Date

SELECT DISTINCT tagname FROM ihRawData WHERE samplingmode=rawbytime
AND timestamp>='11/28/2001' AND timestamp<='11/29/2001'

The Historian OLE DB Provider supports the use of GROUP BY in a SELECT statement. GROUP BY combines records with identical values in the specified field list into a single record. Then, you can compute an aggregate value for the grouped records. The aggregate column does not exist in the actual table. Another calculated column is created with the results.

Example: Group Messages by User Name and Topic

SELECT username, topic, COUNT(*) FROM ihmessages
WHERE timestamp >= '1-dec-2001 00:00:00'
AND timestamp <= '7-dec-2001 00:00:00'
GROUP BY username, topic ORDER BY username, topic

SQL aggregate functions perform a calculation on a set of values in a column and return a single value. For instance, when comparing multiple tags, you could retrieve the minimum (MIN) of the returned minimum values. You usually use aggregate functions with the GROUP BY clause, but it is not required. For more information, see GROUP BY.

STDEV, STDEVP, VAR, and VARP

If a variance is defined as the deviation from an average data set value, and N is the number of values in the data set, then the following equations apply:

VAR = (Sum of Variances)^2 / (N - 1)
VARP = (Sum of Variances)^2 / (N)
STDEV = SquareRoot (VAR)
STDEVP = SquareRoot (VARP)

SQL Aggregate Functions and the CalculationMode Column

For information on the differences between SQL aggregate functions and the CalculationMode columns, refer to How Are Historian Calculation Modes and SQL Aggregate Functions Different?.

Example 1: Retrieve the Total Number of Tags

The following example displays the use of the aggregate COUNT() function without GROUP BY.

SELECT COUNT(*) FROM ihTags

Example 2: Calculate Values for Multiple Tags

The following example displays the use of aggregate functions on grouped rows.

FROM ihrawdata WHERE tagname LIKE '*0001*'
AND timestamp>='28-dec-2001 00:00'
AND timestamp<='29-dec-2001 00:00'
AND samplingmode=interpolated
AND intervalmilliseconds=1h GROUP BY tagname ORDER BY tagname

The following figure displays the results of the previous query. Note the column names (Sum of value, Avg of value, Min of value, and Max of value) returned for the calculated columns.

Figure: Query Results in the Historian Interactive SQL Application

The Historian OLE DB Provider generally returns data with the VARIANT data type. Some OLE DB clients may not understand VARIANT data, however, and will require the data to be returned as an integer, float, or string data type. To accommodate this, the OLE DB Provider includes the functions described in the following table.

Example: Convert Values to Double Float

To convert a value to a double float, you would execute the following query:

select timestamp, to_double(value), quality from ihRawData

A table join is an operation that combines rows from two or more tables. You can join as many tables as you want within one JOIN statement. When you use a table JOIN in a SELECT statement, you must specify the column name and table when selecting the columns that you want to compare. The syntax for table joins follows standard SQL language format. The Historian OLE DB Provider supports the following table joining operations in SELECT statements.

Table joins are a powerful tool when organizing and analyzing data. A few examples are included in this section. However, refer to the documentation for your third-party reporting software for more complete information on building more complex queries.

JOIN Operations Rules

The following rules apply when working with JOIN operations for the Historian OLE DB Provider:

• You cannot join a table with itself.
• You cannot join any table with the ihTrend or ihQuerySettings tables.

Example 1: Perform an Inner Join to Retrieve Only Data With Associated Comments

SELECT d.timestamp, d.tagname, d.value, c.username, c.comment
FROM ihrawdata d INNER JOIN ihcomments c
ON c.tagname=d.tagname AND c.timestamp=d.timestamp
WHERE d.tagname LIKE '*0001*'
ORDER BY d.timestamp, d.tagname, c.username, c.comment

Example 2: Perform a Left Outer Join to Retrieve All Data With and Without Comments

SELECT d.timestamp, d.tagname, d.value, c.comment FROM ihrawdata d
LEFT OUTER JOIN ihcomments c
ON c.tagname=d.tagname AND c.timestamp=d.timestamp
WHERE d.tagname LIKE '*0001*' ORDER BY d.timestamp, d.tagname

Example 3: Perform a Right Outer Join to Retrieve All Comments and Their Accompanying Data

SELECT d.tagname, d.timestamp, d.value, c.comment FROM ihrawdata d
RIGHT OUTER JOIN ihcomments c
ON c.tagname=d.tagname AND c.timestamp=d.timestamp
WHERE d.tagname LIKE '*0001*' ORDER BY d.tagname, d.timestamp

Example 4: Perform a Cross Join

SELECT * FROM ihCollectors CROSS JOIN ihArchives

Example 5: Perform a Cross Join (Older Syntax)

SELECT ihTags.Tagname, iharchives.Filename FROM ihTags, ihArchives

Example 6: Join the ihMessages and ihArchives Tables

This example uses SET StartTime before the SELECT statement. The SET statement is necessary because the timestamp criteria in SELECT do not narrow down the time range for the ihMessages table until after the results have been collected and the join takes place.

SET starttime='1-jan-2000'
SELECT a.starttime, a.endtime, m.*
FROM ihmessages m JOIN iharchives a
ON m.timestamp>=a.starttime
AND m.timestamp<=a.endtime WHERE a.iscurrent=true

Example 7: Interleave Data and Messages by Timestamp

SELECT d.timestamp, m.timestamp, d.tagname, m.messagestring, d.value FROM ihRawData d FULL OUTER JOIN ihMessages m
ON d.timestamp=m.timestamp WHERE d.tagname=simulation00001
AND d.timestamp>='30-nov-2001 00:00:00'
AND d.timestamp<='06-dec-2001 00:00:00'

Example 8: Retrieve the Greatest Values Across All Simulation Tags

In the following example, we join the ihRawData and ihTags tables, because the ihRawData table does not contain the CollectorType column.

SELECT TOP 300 ihRawData.tagname, ihRawData.timestamp, ihRawData.value, ihRawData.Quality FROM ihRawData
INNER JOIN ihTags ON ihRawdata.Tagname = ihTags.Tagname
WHERE ihRawData.tagname LIKE simulation*
AND ihRawData.timestamp>=11/28/2001
AND ihRawData.timestamp<=11/29/2001
AND ihRawData.samplingmode=interpolated AND ihRawData.intervalmilliseconds=1H
AND ihTags.datatype!=FixedString
AND ihTags.datatype!=variablestring
AND ihRawData.quality>0
ORDER BY value DESC, timestamp DESC

Example 9: Join the ihComments and ihRawData Tables

SET starttime='28-nov-2001 08:00', endtime='29-nov-2001 09:00', samplingmode=interpolated, intervalmilliseconds=6m
SELECT d.tagname, d.timestamp, d.value, c.storedontimestamp, c.username, c.datatypehint, c.comment FROM ihcomments c
FULL OUTER JOIN ihrawdata d ON c.tagname=d.tagname
AND c.timestamp=d.timestamp
WHERE d.tagname LIKE '*0001*'
ORDER BY d.tagname, d.timestamp,c.storedontimestamp, c.datatypehint, c.username, c.comment

Example 10: Report by Tag Description

In the following example, we join the ihRawData and ihTags tables to get the Description column from the ihTags table.

SELECT d.timestamp, t.description, d.value, d.quality
FROM ihrawdata d INNER JOIN ihtags t ON d.tagname=t.tagname
WHERE d.tagname LIKE '*0001' ORDER BY d.timestamp, t.description

Example 11: Join Three Tables

SELECT ihTags.Tagname, ihTags.Description, ihRawData.TimeStamp, ihRawData.Value, ihRawData.SamplingMode, ihComments.Comment
FROM ihTags ihTags, ihRawData ihRawData, ihComments ihComments
WHERE ihTags.Tagname = ihRawData.Tagname
AND ihRawData.Tagname = ihComments.Tagname
AND ihRawData.Timestamp = ihComments.Timestamp
AND ihRawData.TimeStamp >= {ts '2002-03-01 09:39:00.000'}
AND ihRawData.TimeStamp <= {ts '2002-03-01 09:41:00.000'}
AND ihRawData.SamplingMode = 'RawByTime'
AND ihTags.Tagname LIKE '%TestTag1%'

Example 12: Perform a Right Join (Older Syntax)

SELECT ihTags.Tagname, ihTags.CollectionInterval, ihCollectors.CollectorName, ihCollectors.DefaultCollectionInterval
FROM ihTzzz|

Example 13: Perform a Left Join (Older Syntax)

SELECT ihTags.Tagname, ihTags.CollectionInterval, ihCollectors.CollectorName, ihCollectors.DefaultCollectionInterval
FROM ihTags ihTags, ihCollectors ihCollectors
WHERE
ihTags.CollectionInterval *=ihCollectors.DefaultCollectionInterval
AND ihTags.Tagname LIKE '%TestTag%'

You need to use quotation marks when you specify a string that contains a space, a comma, or a reserved word. Reserved words are defined by the SQL-92 conventions. Single and double quotes are equivalent in queries.

Example: Use Quotes When a Text String Contains a Space

SELECT * FROM ihtags WHERE comment LIKE 'alert message'

Timestamps appear not just in the TimeStamp columns, but also in columns such as the StartTime, EndTime, and LastModified columns. You can use the date or time, or both the date and time in a SQL statement that contains a timestamp. Valid date and time formats are as follows:

• System short date and time.
• SQL date and time.
• ODBC date and time.

The time format for system short timestamps is the same as the time format defined in the Windows Control Panel.

When entering a query you should use a period as the decimal separator to separate seconds from milliseconds or microseconds.

When using the SQL date and time, you should always use the English abbreviations for the desired month.

If you enter only a start time, the end time is assumed to be now. For example, if you enter starttime > yesterday in a WHERE clause, the end time for the query is now, even if you previously set an end time.

If you enter only an end time, the start time is December 31, 1969, 19:00:00.001. If you use this as the start time, you can overload the Historian server and the provider. For example, if you use timestamp < now, you might cause an overload.

Example 1: Use the System Short Date and Time

SET starttime='02/01/2002 11:00:00'

Example 2: Use the SQL Date and Time

SET starttime='14-sep-2001 11:00:00'

Example 3: Use the ODBC Date and Time

SET starttime={ts '2002-06-20 15:34:08'}

Example 4: Set the Start Time to 4 AM Today

SET starttime='04:00:00'

Example 5: Set the Start Time in Milliseconds

SET starttime='7/12/2011 12:03:16.183'

Example 6: Set the Start Time in Microseconds

SET starttime='7/12/2011 12:03:16.178439'

The following table outlines the date and time shortcuts that you can use to define the start time, end time, and timestamp values in a query.

Example 1: Set the Start Time to the First Day of the Month

SET starttime=bom

Example 2: Retrieve Messages Dated Today

SELECT * FROM ihmessages WHERE timestamp>=today

Optionally, you can add or subtract relative time shortcuts to the absolute times. The following table outlines the relative time shortcuts.

You can use relative time shortcuts when defining time intervals. For instance, use these shortcuts when you specify a value for the IntervalMilliseconds column.

Example 1: Set the Start Time to 10 Days Before Yesterday and End Time to Today

SET starttime=yesterday-10d, endtime=today
SELECT * FROM ihQuerySettings

Example 2: Retrieve the Previous 24 Hours of Messages

SELECT * FROM ihMessages WHERE timestamp>=Now-24h

Example 3: Select Data Starting at 1AM Yesterday and Ending Now

SELECT * FROM ihrawdata WHERE timestamp>=yesterday+1h AND timestamp<=now

Example 4: Retrieve Raw Data With a 1 Hour (3600000 Milliseconds) Interval Between Returned Samples

SELECT * FROM ihrawdata WHERE intervalmilliseconds=1h

Example 5: Retrieve Raw Data With a 100 Microseconds Interval Between Returned Samples

SELECT * FROM ihrawdata WHERE intervalmilliseconds=100micro and starttime>= '7/12/2011 12:03:16.100000' and endtime<=' 

Example 6: Retrieve This Week's Output to Date

SET starttime=Sun, endtime=Now, intervalmilliseconds=1d, samplingmode=rawbytime
SELECT tagname, SUM(value) FROM ihRawData WHERE tagname LIKE *00* GROUP BY tagname

The Historian OLE DB Provider supports the use of comparison symbols within SQL statements. The following table outlines the comparison operators supported.

The Historian OLE DB Provider does not support a literal on the left side of the comparison operator. For example, this statement would fail:

SELECT DISTINCT tagname FROM ihRawData WHERE 50>Value

SELECT DISTINCT tagname FROM ihRawData WHERE Value>50

The following examples show other possible uses of these comparison symbols in SELECT statements.

Example 1: Retrieve Tags with a High EGU Greater Than 300

SELECT DISTINCT tagname, loengineeringunits, hiengineeringunits
FROM ihTags WHERE hiengineeringunits > 300

Example 2: Retrieve Tags with a Specific Description

SELECT tagname, description FROM ihTags WHERE description = "aa"

Example 3: Retrieve All Samples Where the Value Exceeds Query Supplied Values

SELECT timestamp, tagname, value FROM ihRawData WHERE samplingmode=rawbytime AND value>75

Example 4: Retrieve All Samples Where the Value is Between Query Supplied Values

SELECT timestamp, tagname, value FROM ihRawData WHERE samplingmode=lab AND value BETWEEN 25 AND 75

Example 5: Retrieve All Tag Names Starting with an A or B

SELECT * FROM ihtags WHERE tagname < 'C'

The Historian OLE DB Provider supports the use of these logic operators in SQL statements:

• AND
• OR
• NOT

The following examples show possible uses of these operators in SELECT statements.

Example 1: Use the AND Logical Operator

SELECT * FROM ihTags WHERE Tagname LIKE 'Simulation*' AND CollectionInterval<3000

Example 2: Use the OR Logical Operator

SELECT * FROM ihTags WHERE Tagname LIKE 'ComputerName.Simulation*' OR tagname LIKE '*String*'

Example 3: Use the NOT Logical Operator

SELECT * FROM ihTags WHERE NOT Datatype=SingleFloat

Example 4: Use the NOT Logical Operator With a LIKE Expression

SELECT * FROM ihTags WHERE Tagname NOT LIKE '*String*'

Parentheses control the order of evaluation of the logical operators in an expression. The Historian OLE DB Provider supports parentheses in a WHERE clause. You can use multiple sets of parentheses, and nest parenthetical expressions.

Example 1: Use Parentheses

SELECT * FROM ihTags
WHERE (tagname LIKE *001 AND description="aa") OR tagname LIKE *002

Example 2: Use Parentheses with Logical Operators and Timestamps

SELECT * FROM ihRawData WHERE tagname=Simulation00001 AND (Timestamp=>Tu AND Timestamp<=Wed OR Timestamp>=Fri AND time

Example 3: Use Multiple Sets of Parentheses

SELECT * FROM ihtags
WHERE (tagname LIKE '*001*' AND description LIKE '*sim*') OR
(tagname LIKE '*02*' AND (description LIKE '*sec*' OR description LIKE '*sim*'))

The following table outlines the supported SQL variables and settings that you can use in a SET statement. If you do not change any variables using the SET statement or a WHERE clause in your SELECT statement, the Historian OLE DB Provider assumes default session variables. You can apply any of the variables described in the following table to the current session. In turn, these settings are used when retrieving information from the Historian database tables. SET variables persist from statement to statement.