Supported SELECT Statements Syntax | Historian 2022 Documentation

You can use a WHERE clause 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 can search all rows of data in the ihTags table, where the DataType column equals SingleFloat. In another instance, you can find all tags that belong to a particular collector. Or, you can search for all tags with a certain poll rate, or range of poll rates, or ones with polling disabled.

You can provide maximum 200 conditions in a SELECT statement.

For more information on the columns for each individual Historian table, refer to 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

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

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

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.

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 must include that column name in ORDER BY.

As a more common example, when requesting timestamps with data, 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. By default, the ascending order is considered. 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.


Abbreviation	Description
`ASC`	Specifies that the values must be sorted in ascending order, from lowest value to highest value.
`DESC`	Specifies that the values must be sorted in descending order, from highest value to lowest value.

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

Example 1: Retrieve Collectors in Descending Order Sorted by the Collectorname Column

SELECT * FROM ihcollectors ORDER BY collectorname DESC

Example 2: Retrieve Messages in Ascending Order Sorted by Timestamp 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

With the TOP predicate, you can limit the number of rows returned to a specified number or percentage of rows. And then, 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, suppose 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.

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

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 and/or after the text that you want to search for. Use an asterisk (*) for multiple unknown characters in a search string. Use a question mark (?) for a single unknown character.

Note: You can also use a percentage (%) to select all tags that contain a specific string in the tag name and an underscore (_) to select all tags when you are unsure of only one character in the tag name. You must enclose these wildcard characters in single quotes (for example, '%' or '_') when you use them in Historian tag names, but do not use single quotes if you want them to be treated as wildcards in SQL.

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

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'

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 can 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.

Table 1. Supported Aggregate Functions
Function	Description
`AVG`	Returns the average of the values in a group. Null values are ignored.
`COUNT`	Returns the number of items in a group. Null values are not ignored.
`MAX`	Returns the maximum value in a group. Null values are ignored.
`MIN`	Returns the minimum value in a group. Null values are ignored.
`SUM`	Returns the sum of all the values in a group. `SUM` can be used with numeric columns only. Null values are ignored.
`STDEV`	Returns the statistical standard deviation of all values in a group. Null values are ignored.
`STDEVP`	Returns the statistical standard deviation for the population for all values in a group. Null values are ignored.
`VAR`	Returns the statistical variance of all values in a group. Null values are ignored.
`VARP`	Returns the statistical variance for the population for all values in a group. Null values are ignored.

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)

Example 1: Retrieve the Total Number of Tags

SELECT COUNT(*) FROM ihTags

Example 2: Calculate Values for Multiple Tags

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 this query. Note the column names (Sum of value, Avg of value, Min of value, and Max of value) returned for the calculated columns.

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.

Table 2. Conversion Functions
Function	Description
`to_double (column)`	Converts the specified `column` to a double float data type.
`to_integer (column)`	Converts the specified `column` to a single integer data type.
`to_string (column)`	Converts the specified `column` to a string data type.

Note:

You must edit the SQL statement manually to add conversion functions.
You can also use the fully qualified column name (for example, ihRawData.value).
Conversion functions are not available in WHERE or JOIN (ON) clauses.
Conversion functions cannot be used within aggregate functions.

Example: Convert Values to Double Float

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.

Table 3. Supported Join Operations
Supported Join Feature	Description
Inner Join	Combines records from two tables whenever there are matching values.
Left Join or Left Outer Join	Returns all of the rows from the left (first) of two tables, even if there are no matching values for records in the right (second) table.
Right Join or Right Outer Join	Returns all of the rows from the right (second) of two tables even if there are no matching values for records in the left (first) table.
Full Join or Outer Join	Returns all rows in both the left and right tables. Any time a row has no match in the other table, `SELECT` list columns from the other table contain null values. When there is a match between the tables, the entire result set row contains data values from the base tables.
Cross Join	Returns all rows from the left table. Each row from the left table is combined with all rows from the right table.
Old Join syntax	Simply selects columns from multiple tables using the `WHERE` clause without using the `JOIN` keyword.

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.

The following examples display different types of joins with the ihComments table. Comments themselves are not usually that useful unless they are combined with data, as you do with the JOIN statements in the following examples.

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 must 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 and/or 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'


Time Segment	Meaning
`now`	Now (the time and date that you execute the query)
`today`	Today at midnight
`yesterday`	Yesterday at midnight
`mon`	The previous Monday at midnight
`tues`	The previous Tuesday at midnight
`wed`	The previous Wednesday at midnight
`thurs`	The previous Thursday at midnight
`fri`	The previous Friday at midnight
`sat`	The previous Saturday at midnight
`sun`	The previous Sunday at midnight
`boy`	First day of year at midnight
`eoy`	Last day of year at midnight
`bom`	First day of month at midnight
`eom`	Last day of month at midnight

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.

Table 4. Relative Date and Time Shortcuts
Time Segment	Meaning
`s`	Second
`m`	Minute
`h`	Hour
`d`	Day
`w`	Week
`micro`	Microsecond

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

Note: You cannot use relative time shortcuts to add or subtract microseconds to or from absolute times.

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

Table 5. Expression Comparisons
Comparison Symbol	Meaning
`<`	Less Than
`>`	Greater Than
`<=`	Less Than or Equal
`>=`	Greater Than or Equal
`=`	Equal
`!=`	Not Equal
`!>`	Not Greater Than
`!<`	Not Less Than
`BETWEEN x AND y`	Between the values `x` and `y` inclusive, where `x` and `y` are numeric values

A literal on the left side of the comparison operator is not supported. For example, this statement would fail:

SELECT DISTINCT tagname FROM ihRawData WHERE 50>Value

But the following statement succeeds since the Value column is to the left of the > operator:

SELECT DISTINCT tagname FROM ihRawData WHERE Value>50

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 following logic operators are supported:

AND
OR
NOT

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 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*'))