Supported SQL Syntax

Important: You do not have the latest version of Historian! You are missing out on the newest capabilities and enhanced security. For information on all the latest features, see the Historian product page. For more information on upgrades, contact your GE Digital sales agent or e-mail GE Digital Sales Support. For the most up-to-date documentation, go here.

About OLE DB Provider and SQL Syntax

The Historian OLE DB Provider supports the SET and SELECT statements in SQL queries. The supported statements follow the standard SQL-92 conventions. Consistent with SQL standards, these statements are not case-sensitive.

Some reporting packages, such as Crystal Reports, hide the SQL syntax by allowing you to use experts and wizards. However, familiarity with SQL syntax may help you in troubleshooting and tuning your SQL commands.

The Historian OLE DB Provider currently does not allow SQL inserts, updates, deletes, or commits. There is also no event notification, because that is typically used along with inserting, updating, or deleting, and these operations are not allowed.

This chapter describes the supported SQL syntax for SELECT and SET statements. While the Historian Interactive SQL application allows SET statements, not all reporting packages do. For instance, Crystal Reports does not allow SET statements. In this case, you would set query parameters with a WHERE command in your SELECT statement instead. The WHERE clause overrides the SET statement for that query. Everything that can be done in a SET statement can be done using a WHERE clause.

The following figure shows a simple SELECT statement. With a SELECT statement, you can specify the Historian table and columns from which you want to retrieve data. The Historian OLE DB Provider establishes the server name at connect time. You can filter the data returned from SELECT by specifying a filter option in the WHERE clause.

Figure: Sample SELECT Statement

The rest of this chapter details how to create statements like this and gives examples of how to build more complex, yet efficient SQL statements. This chapter is only intended to get you started with creating SQL queries with the Historian OLE DB Provider. It assumes that you are familiar with the SQL language and SQL-92 conventions. For more detailed information on the SQL language or how to create queries with your third-party reporting software, refer to your third-party documentation.

The Historian OLE DB Provider does not support the full SQL-92 syntax. The following sections document the supported syntax.

NOTE: String data types are not supported.

General Guidelines for Building a Query

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.

Table 1. Historian Database Tables
Table Name Description
ihTags Table Contains Historian tag configuration information.
ihArchives Table Contains Historian archive configuration information, plus performance statistics for each archive.
ihCollectors Table Contains configuration and status information for each collector connected to the Historian server.
ihMessages Table Contains Historian messages such as alerts, informational topics, and connection information contained in the audit log.
ihRawData Table Contains collected data for each tag in the Historian server. It contains not just raw data, but also calculated and interpolated data.
ihComments Table Contains the comments associated with the Historian data.
ihTrend Table Another way to look at collected data. Contains a row of data for each unique timestamp. You can use this table to look at your data at a summarized level. You would typically use this table to compare multiple tags with the same timestamp.
ihQuerySettings Table Contains a set of parameters that apply to all queries you make in that session, unless overridden by a WHERE clause.
ihCalculationDependencies Contains the calculation dependencies for tags.
ihAlarms Table Contains collected alarm and event data.
ihEnumeratedSets Table Contains information about enumerated sets.
ihEnumeratedStates Table Contains information about enumerated states.
ihUserDefinedTypes Table Contains information about user-defined data types.
ihFields Table Contains information about fields used in user-defined types.

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 2. Supported SELECT Statement Functionality
Functionality Description
WHERE Clauses Defines a condition on which to select the records or set of query parameters.
Result Sorting Sorts the returned records by one or more columns, ascending or descending (ORDERBY).
TOP Percent Limits the number of returned records to the specified top number, or percent of rows.
LIKE Expressions Allows you to query records using wild cards.
AS Condition Allows you to give a different name for a column or table than what is stored in the database.
Eliminate Duplicate Return Rows Eliminates duplicate records where all columns are equal (DISTINCT).
Group Records Combines records with identical values in the specified field list into a single record (GROUP BY).
SQL Aggregate Functions Performs a calculation on a set of values and returns a single value (AVG, COUNT, MAX, MIN, SUM, STDEV, STDEVP, VAR, VARP).
Table Joins Combines columns from two or more tables.
Quotation Marks Specifies a string that contains a space, comma, or reserved word.
Timestamp Formats Specifies the types of supported timestamps.
Date and Time Shortcuts Lists the date shortcuts that you can use in a SQL statement.
Comparison Operators Describes the use of comparison symbols within SQL statements.
Logical Operators Allows queries to be developed using Boolean logic (OR, AND, NOT).
Parenthetical Expressions Controls 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.

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 3. 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 4. 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)

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

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 5. 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. For more information, see SQL 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 6. 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 7. 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 8. 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 9. 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*'))

Supported SET Statement Syntax

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

SET Variables

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.

Some session variables that you define with the SET statement accept abbreviations. You must type at least the abbreviation for the statement to work. For instance, for the CalculationMode setting you can enter the abbreviation Interp for the Interpolated setting. As a minimum, you have to at least enter the letters Interp as the abbreviation for the CalculationMode. The accepted abbreviations are highlighted in bold in the following table.
Table 10. SET Statement Variables
Variable Description
StartTime A valid date and time string, such as:
  • StartTime = '14-sep-200111:00:00'
  • StartTime = Now -1h
  • StartTime = '02/01/199811:00:00'
  • StartTime = {ts '2002-06-20 15:34:08'}
  • StartTime = '7/12/201112:03:16.100000'
Default Setting: Two hours prior to execution of the query.
EndTime A valid date and time string, such as:

EndTime = '14-sep-200112:00:00'

Default Setting: The current time that you execute the query.
SamplingMode String that represents the mode of sampling data from the archive:
  • CurrentValue
  • Interpolated
  • InterpolatedtoRaw
  • RawByTime
  • RawByNumber
  • Calculated
  • Lab
  • LabtoRaw
  • Trend
  • TrendtoRaw
  • Trend2
  • TrendtoRaw2
  • RawByFilterToggle
Default Setting: Calculated
Direction String that represents the direction of data sampling from the archive, beginning at the start time. Direction applies to the RawByTime and RawByNumber sampling modes:
  • Forward
  • Backward
Default Setting: Forward
NumberOfSamples Any positive integer that represents the number of samples from the archive to retrieve. Do not enter a thousands separator. For example, enter 1000 and not 1,000.

Samples are evenly spaced within the time range defined by start and end times for most sampling modes. For the RawByNumber sampling mode, the NumberOfSamples attribute determines the maximum number of values to retrieve. For the RawByTime sampling mode, the NumberOfSamples is ignored.

Default Setting: 0 (use IntervalMilliseconds)

IntervalMilliseconds Any positive integer that represents the interval (in milliseconds) between returned samples.

For example:

  • If you run a query with 'IntervalMilliseconds = 100', it returns samples in 100-millisecond intervals.
  • If you run a query with 'IntervalMilliseconds = 100micro', it returns samples in 100-microsecond intervals.
Default Setting: 60000 (one minute)
CalculationMode The CalculationMode column only applies if the SamplingMode is set to Calculated. It represents the type of calculation to perform on archive data:
  • Average
  • StandardDeviation
  • Total
  • Minimum
  • MaximumCount
  • RawAverage
  • RawStandardDeviation
  • RawTotal
  • MinimumTime
  • MaximumTime
  • Count
  • TimeGood
  • FirstRawValue
  • FirstRawTime
  • LastRawValue
  • LastRawTime
  • TagStats
Default Setting: Average
FilterTag A valid tagname used to define the filter, if specified. For example, a FilterTag might be defined as:

FilterTag = 'SimulationString00001'

Only a single tag ID can be specified in the FilterTag. Wildcards are not supported. FilterTag is used in conjunction with FilterValue, FilterComparisonMode, and FilterMode.

Default Setting: An empty space (meaning FilterTag is not used)

FilterMode String that represents the type of time filter:
  • ExactTime
  • BeforeTime
  • AfterTime
  • BeforeAndAfterTime

For example, AfterTime indicates that the filter condition should be True starting at the timestamp of the archive value that triggered the True condition and leading up to the timestamp of the archive value that triggered the False condition. FilterMode is used in conjunction with FilterValue, FilterComparisonMode, and FilterTag.

Default Setting: BeforeTime

FilterComparisonMode String that represents the type of comparison to be made on the filter comparison value:
  • Equal
  • EqualFirst
  • EqualLast
  • NotEqual
  • LessThan
  • GreaterThan
  • LessThanEqual
  • GreaterThanEqual
  • AllBitsSet
  • AnyBitSet
  • AnyBitNotSet
  • AllBitsNotSet
If FilterTag and FilterComparisonValue are supplied in the SET statement, time periods are filtered from the results where the filter condition is False. FilterComparisonMode is used in conjunction with FilterValue, FilterMode, and FilterTag.

Default Setting: Equal

FilterExpression An expression which includes multiple filter conditions. FilterExpression can be used instead of FilterTag, FilterComparisonMode and FilterValue.

FilterExpression = 'BatchID=B1'

While using FilterExpression, the expression is passed within single quotes, and for complex expressions we write the conditions within a parenthesis. There is no maximum length for FilterExpression.
FilterValue String that represents the value with which to compare the filter tag to determine the appropriate filter times. Wildcards are not supported. Do not use a comma for the thousands separator.

For example, a sample FilterValue setting might be:

FilterValue = 'ABCD-1086031382099'

The FilterValue is used in conjunction with FilterComparisonMode, FilterMode, and FilterTag.

Default Setting: An empty space (meaning filtering is not used)

TimeZone String that represents the type of time zone that should be applied to timestamps:
  • Client
  • Server
  • Explicit bias number (number of minutes from GMT)

For example, an explicit bias number of 300 represents 300 minutes from GMT.

Note: Time zones are not supported on Windows 9x computers.

Default Setting: Client

DaylightSavingTime Flag that indicates whether Daylight Saving Time logic should be applied to timestamps:
  • True
  • False
Default Setting: Date and time settings in your Windows Control Panel
RowCount Positive number that indicates the maximum number of rows that can be returned. A listing of 0 indicates there is no limit to the number of rows returned.

Default Setting: 5000

SET Statements and Variables Examples

Example 1: Perform a Simple SET

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. For instance, if you do not specify a start and end time for your collected data, the data output from a SELECT statement would be the last two hours prior to execution of the query.

For example, if you want to SELECT all of the messages from the ihMessages table for the last day, you would need to explicitly state that you want the messages from the last day in the query. Otherwise, only the messages from the last two hours are displayed when you run the query, since that is what the default assumes.

SET statement variables persist during a session until changed. You can combine the SET statement on the same line as the SELECT statement. The following examples show sample uses of the SET statement.

SET samplingmode=currentvalue
Example 2: Perform Multiple SETs
SET starttime='14-sep-2001 11:00:00', endtime='14-sep-2001 12:00:00', samplingmode=interpolated, intervalmilliseconds=
Example 3: Prepare for a RawByTime Query
SET starttime='14-sep-2001 11:00:00', endtime='14-sep-2001 12:00:00', samplingmode=rawbytime
Example 4: Prepare for a RawByNumber Query
SET starttime='14-sep-2001 11:00:00', samplingmode=rawbynumber, numberofsamples=10, direction=backward
Example 5: Prepare for One Hour Minimums
SET starttime='15-sep-2001 00:00:00', endtime='16-sep-2001 00:00:00', samplingmode=calculated, intervalmilliseconds=36
Example 6: Prepare for a Filtered Data Query
SET starttime='14-sep-2001 11:00:00', endtime='14-sep-2001 12:00:00', samplingmode=current, filtertag='MY_SERVER.simul
Example 7: Throttle Results with a SET Statement
SET ROWCOUNT = 4
SELECT Tagname FROM ihTags

Combined SET and SELECT Statements

The Historian OLE DB Provider allows you to execute one SELECT statement and one SET statement per query. Enter a space or a line break to indicate the end of a statement in a query. Do not use a semicolon (;) at the end of the line or statement, as it is not necessary with the Historian OLE DB Provider.

Example 1: Use SET and SELECT Statements on the Same Line
SET samplingmode=interpolated SELECT * FROM ihquerysettings
Example 2: Use SET and SELECT Statements on Different Lines
SET samplingmode=calculated, starttime=yesterday, endtime=today
SELECT * FROM ihquerysettings

Parameterized SQL Queries

Parameterized SQL queries allow you to place parameters in an SQL query instead of a constant value. A parameter takes a value only when the query is executed, which allows the query to be reused with different values and for different purposes. Parameterized SQL statements are available in some analysis clients, and are also available through the Historian SDK.

For example, you could create the following conditional SQL query, which contains a parameter for the collector name:

SELECT* FROM ihtags WHERE collectorname=? ORDER BY tagname

If your analysis client were to pass the parameter iFIX_Albany along with the query, it would look like this when executed in Historian:

SELECT* FROM ihtags WHERE collectorname='iFIX_Albany' ORDER BY tagname

The benefit of parameterized SQL queries is that you can prepare them ahead of time and reuse them for similar applications without having to create distinct SQL queries for each case. The previous example, for instance, could be used in any context where you want to get tags from a collector. Parameterized queries can also be used with dynamic data, where you don't know what the values will be until the statement is executed.

If your analysis client supports parameterized queries, it will automatically pass the parameter data along with a named query for Historian to process. In the case of multiple parameters, the analysis client will read the named query, and order the parameters to match.

Consult your analysis client's documentation for support and usage of parameterized SQL queries.

Note: You cannot use parameters to substitute table names or columns in a query.

Multiple Parameters

To create a query with multiple parameters, simply place a question mark (?) for every parameter you wish to substitute a value for in the query. For example, if you wanted a SQL query to match two WHERE conditions, collectorname and tagname, you could use the following parameterized query:

SELECT* FROM ihtags WHERE collectorname=? AND tagname like ? ORDER BY tagname

When executed, the parameterized SQL query will add the parameters as they are received from the analysis application. In the previous example, the collectorname parameter would be received first, followed by the tagname parameter. Your analysis client will order the parameters based on the query it is running.

Note: If you wish to enter wildcard data in your parameterized queries, you must include the wildcard characters as part of the parameter. For instance, in the previous example, if you wanted to find any tagnames with the string iFIX in them, you would pass it the *iFIX* parameter.

Query Performance Optimization

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.