Supported SQL Syntax
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.
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.
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 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 alarms and events 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.
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.
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 aSELECT
statement. You can specify a condition for any column in the table using theWHERE
clause.For example, you could use a simple
WHERE
clause to search all rows of data in the ihTags table, where theDataType
column equalsSingleFloat
. 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 aSELECT
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 ofCalculated
toInterpolated
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 RangeSELECT* 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 aSELECT
statement. If you do not specifyORDER 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 theCollectorName
column, you would need to include that column name inORDER BY
. As a more common example, when requesting timestamps with data, you should use theTimestamp
column withORDER 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 processesORDER BY
before it performs anyRowCount
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 ColumnSELECT * FROM ihcollectors ORDER BY collectorname DESC
Example 2: Retrieve Messages in Ascending Order Sorted by Time and Other ColumnsSELECT * 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 aSELECT
statement. With theTOP
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 theTOP
predicate, enter the rest of the query. Typically, you includeORDER 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 theRowCount
. For instance, say you want the top 30 percent of rows from a query that can return a possible 10,000 rows, but theRowCount
is set to 1000. The percentage logic processes the 3000 rows first, then it reduces the number to 1000 rows, as specified byRowCount
. The final result returns 1000 rows, even though the top 30 percent is processed first. Use aSET
statement orWHERE
clause to change or disable theRowCount
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 theihMessages
table.Example 1: Return the Top 40 Tags in Alphabetical OrderSELECT TOP 40 * FROM ihtags ORDER BY Tagname
Example 2: Return the Top 10 Most Recent MessagesSELECT TOP 10 timestamp, topic, username, messagestring FROM ihmessages WHERE timestamp<Now ORDER BY timestamp DESC
Example 3: Return the Top 10 Percent, RowCount DisabledSET 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 theLIKE
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 ReplacementSELECT * FROM ihtags WHERE tagname LIKE *.Simulation* ORDER BY tagname SELECT * FROM ihtags WHERE tagname LIKE %.Simulation%
Example 2: Use LIKE With Single Character ReplacementSELECT * 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 useAS
in all columns and tables except the ihTrend table. In the ihTrend table, you can only useAS
with theTimeStamp
column.Example: Set the Output Column NameSELECT status, collectorname AS Name, collectortype, status AS 'The Status', collectordescription FROM ihcollectors
- DISTINCT
-
The Historian OLE DB provider supports the use of
DISTINCT
in aSELECT
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 ArchiveSELECT DISTINCT datatype FROM ihtags
Example 2: Retrieve the Set of Tags With Raw Data Samples on a Specific DateSELECT 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 aSELECT
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 TopicSELECT 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 (
The Historian OLE DB provider supports the aggregate functions described in the following table.MIN
) of the returned minimum values. You usually use aggregate functions with theGROUP BY
clause, but it is not required. For more information, see Group By.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 withoutGROUP 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 understandVARIANT
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 specifiedcolumn
to a double float data type.to_integer (column)
The to_integer
function converts the specifiedcolumn
to a single integer data type.to_string (column)
The to_string
function converts the specifiedcolumn
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
orJOIN (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 tableJOIN
in aSELECT
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 inSELECT
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 theJOIN
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.
JOIN
statements in the following examples.Example 1: Perform an Inner Join to Retrieve Only Data With Associated CommentsSELECT 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 CommentsSELECT 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 DataSELECT 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 JoinSELECT * 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 usesSET StartTime
before theSELECT
statement. TheSET
statement is necessary because the timestamp criteria inSELECT
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 TimestampSELECT 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 theihRawData
andihTags
tables, because theihRawData
table does not contain theCollectorType
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 TablesSET 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 theihRawData
andihTags
tables to get theDescription
column from theihTags
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 TablesSELECT 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 SpaceSELECT * FROM ihtags WHERE comment LIKE 'alert message'
- Timestamp Formats
Timestamps appear not just in the
TimeStamp
columns, but also in columns such as theStartTime
,EndTime
, andLastModified
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 aWHERE
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
Example 1: Use the System Short Date and TimeSET
statement.
Example 2: Use the SQL Date and TimeSET starttime='02/01/2002 11:00:00'
Example 3: Use the ODBC Date and TimeSET starttime='14-sep-2001 11:00:00'
Example 4: Set the Start Time to 4 AM TodaySET starttime={ts '2002-06-20 15:34:08'}
Example 5: Set the Start Time in MillisecondsSET starttime='04:00:00'
Example 6: Set the Start Time in MicrosecondsSET starttime='7/12/2011 12:03:16.183'
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.
Example 1: Set the Start Time to the First Day of the MonthTable 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 2: Retrieve Messages Dated TodaySET starttime=bom
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
Example 2: Retrieve the Previous 24 Hours of MessagesSET starttime=yesterday-10d, endtime=today SELECT * FROM ihQuerySettings
Example 3: Select Data Starting at 1AM Yesterday and Ending NowSELECT * FROM ihMessages WHERE timestamp>=Now-24h
Example 4: Retrieve Raw Data With a 1 Hour (3600000 Milliseconds) Interval Between Returned SamplesSELECT * FROM ihrawdata WHERE timestamp>=yesterday+1h AND timestamp<=now
Example 5: Retrieve Raw Data With a 100 Microseconds Interval Between Returned SamplesSELECT * FROM ihrawdata WHERE intervalmilliseconds=1h
Example 6: Retrieve This Week's Output to DateSELECT * FROM ihrawdata WHERE intervalmilliseconds=100micro and starttime>= '7/12/2011 12:03:16.100000' and endtime<='
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
andy
inclusive, wherex
andy
are numeric valuesThe 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 theValue
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
Example 2: Retrieve Tags with a Specific DescriptionSELECT DISTINCT tagname, loengineeringunits, hiengineeringunits FROM ihTags WHERE hiengineeringunits > 300
Example 3: Retrieve All Samples Where the Value Exceeds Query Supplied ValuesSELECT tagname, description FROM ihTags WHERE description = "aa"
Example 4: Retrieve All Samples Where the Value is Between Query Supplied ValuesSELECT timestamp, tagname, value FROM ihRawData WHERE samplingmode=rawbytime AND value>75
Example 5: Retrieve All Tag Names Starting with an A or BSELECT timestamp, tagname, value FROM ihRawData WHERE samplingmode=lab AND value BETWEEN 25 AND 75
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
Example 1: Use the AND Logical OperatorSELECT
statements.
Example 2: Use the OR Logical OperatorSELECT * FROM ihTags WHERE Tagname LIKE 'Simulation*' AND CollectionInterval<3000
Example 3: Use the NOT Logical OperatorSELECT * FROM ihTags WHERE Tagname LIKE 'ComputerName.Simulation*' OR tagname LIKE '*String*'
Example 4: Use the NOT Logical Operator With a LIKE ExpressionSELECT * FROM ihTags WHERE NOT Datatype=SingleFloat
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
Example 1: Use ParenthesesWHERE
clause. You can use multiple sets of parentheses, and nest parenthetical expressions.
Example 2: Use Parentheses with Logical Operators and TimestampsSELECT * FROM ihTags WHERE (tagname LIKE *001 AND description="aa") OR tagname LIKE *002
Example 3: Use Multiple Sets of ParenthesesSELECT * FROM ihRawData WHERE tagname=Simulation00001 AND (Timestamp=>Tu AND Timestamp<=Wed OR Timestamp>=Fri AND time
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.
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.Variable | Description |
---|---|
StartTime | A valid date and time string, such as:
|
EndTime | A valid date and time string, such as:
|
SamplingMode | String that represents the mode of sampling data from the archive:
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 |
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 Default Setting:
|
IntervalMilliseconds | Any positive integer that represents the interval (in milliseconds) between returned samples. For example:
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 |
FilterTag | A valid tagname used to define the filter, if specified. For example, a FilterTag might be defined as:
Only a single tag ID can be specified in the Default Setting: An empty space (meaning |
FilterMode | String that represents the type of time filter:
For example, Default Setting:
|
FilterComparisonMode | String that represents the type of comparison to be made on the filter comparison value:
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: |
FilterExpression | An expression which includes multiple filter conditions. FilterExpression can be used instead of FilterTag , FilterComparisonMode and FilterValue .
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
The 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:
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:
|
DaylightSavingTime | Flag that indicates whether Daylight Saving Time logic should be applied to timestamps:
|
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:
|
SET Statements and Variables Examples
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.
Example 1: Perform a Simple SET
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.
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.
*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
orROWCOUNT
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.