WHERE Clauses

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.

Note: A maximum of 200 conditions may be supplied in a SELECT statement.

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.