Supported SQL Syntax

Using SQL statements, you can retrieve information from the columns and rows in a specified table or tables. You are only limited by the amount of memory on your system when determining the number of data rows that you can retrieve from a table.

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.

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.

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.

ORDER BY

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.

Table 2. ORDER BY Abbreviations

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

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

TOP

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

LIKE

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.

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

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

AS

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

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'

GROUP BY

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

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.

The Historian OLE DB provider supports the aggregate functions described in the following table.

Table 3. 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

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.

Conversion Functions

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 4. Conversion Functions

Function	Description
to_double (column)	The to_double function converts the specified column to a double float data type.
to_integer (column)	The to_integer function converts the specified column to a single integer data type.
to_string (column)	The to_string function 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

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

select timestamp, to_double(value), quality from ihRawData

JOIN

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 5. 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%'

Quotation Marks

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'

Timestamp Formats

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.

The following examples show how each time format appears in a sample SET statement.

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'

Date and Time Shortcuts

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.

Table 6. Date and Time Shortcuts

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

Relative Date and Time Shortcuts

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

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

Comparison Operators

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

Table 8. 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

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

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

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'

Logical Operators

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

Parenthetical Expressions

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 use of SET statements is not mandatory, since query parameters can also be specified in a WHERE clause. However, SET statements can make your queries more readable. By using SET statements, you can save time by simplifying SELECT queries, because you do not have to retype query parameters each time you issue a new SELECT statement. The SET parameters persist for the entire session.

With a SET statement you can define various defaults for your queries to use, such as:

• The starting date and time of the selected data
• The ending date and time
• The calculation mode
• The number of rows returned
• The data sampling mode

For more information, see ihQuerySettings Table.

When entering numbers, do not use a thousands separator. For example, if you were setting a collection interval to 7,000 milliseconds, the following statement would be correct.

Example: Correct SET Without Comma to Separate Thousands Place

SET IntervalMilliseconds = 7000

Multiple SET statements in the same command are not supported. Combine multiple variables in the same SET statement.

Incorrect

For instance, improper SET statements would be:

SET starttime=yesterday-10d
SET endtime=today
SET samplingmode=interpolated

Correct

The correct way of writing the above SET statement is as follows:

SET starttime=yesterday-10d, endtime=today, samplingmode=interpolated

To optimize query performance, be sure to do the following:

• Perform GROUP BY on the server whenever available. For instance, Crystal Reports gives you the option to group on the server as opposed to the client.
• Use DISTINCT to eliminate duplicate rows.
• Be specific when specifying tag names. For instance, when using wildcards, be as specific as possible.
• Limit the duration between start and end times.
• Get as precise a data type as possible to improve storage efficiency and allow reporting tools such as Crystal Reports to properly format the data in reports.
• Do not rely on TOP or ROWCOUNT to optimize performance, since they do not change the load on the archive or network but instead they just limit what is returned to the caller.