ODBC Interface: SQL Queries

 

Supported SQL syntax follows the format:

 SELECT [DISTINCT | ALL] columnspecifier-1, columnspecifier-2, ...

 FROM tablename-1, tablename-2, ...

 [WHERE where-expression]

 [ORDER BY columnspecifier-1 [ASC | DESC], ...]

 

For the above ...

•    The column specifier is either table-name.*  to indicate all columns in a table or a specific column-name in the form: table-name.column-name.

•    The table name need only be included if you are selecting from more than one table.

•    The table name is either the table name or 'table-name AS alias-name'.   Quotes may be used around table or column names, and must be used if the names contain special characters such as ':'.   When an alias-name is specified it may be used in place of the table-name in the column specifier.

•     The where-expression  filters the data that will be retrieved by the query.  It may contain references to columns, use comparison operators, use functions ABS, LENGTH, UPPER, LOWER, CONCAT, CASE, SQRT, INTEGER, and use keywords AND, NOT, and OR.   Note: The wildcard for a SQL query is a percent symbol, %.

 

 When more than one table is specified, the tables are automatically joined based on their Timestamp columns and MUST have matching time periods.  See the optional step in Configuring Your Application to be an ODBC Server for a discussion of time periods.

 A join expression may be used in place of a table list, but it is only parsed to extract the tables specified; the actual join expression is ignored.

 

Examples:

 

Example 1:

To retrieve all logged data from a driver called ModDriver1 over a particular one-minute period:

 

SELECT * FROM ModDriver1

WHERE Timestamp > '2007-03-01 16:54:00'

AND Timestamp < '2007-03-01 16:55:00'

 

This will return a result set with columns Timestamp, ErrorValue, FailedCount, FailedRetryCount, SuccessCount, Quality, ResponseTime, and ErrorAddress.

 

Example 2:

To retrieve weekly minimum values from two analog input tags called ai1 and ai2 over the course of November 2006, sorted with most recent values first:

 

SELECT "ai1:1W"."Timestamp",

       "ai1:1W"."Value:Minimum" AS ai1Min,

       "ai2:1W"."Value:Minimum" AS ai2Min FROM "ai1:1W", "ai2:1W"

WHERE  "ai1:1W"."Timestamp" >= '2006-11-01 00:00:00'

AND    "ai1:1W"."Timestamp" < '2006-12-01 00:00:00'

ORDER BY "ai1:1W"."Timestamp"  DESC

 

This will return a result set with columns Timestamp, ai1Min, and ai2Min.

 

Example 3:

 To select all the values recoded by a tag named Tank1Level for one hour on April 1 2008:

 

SELECT Tank1Level.Timestamp, Tank1Level.Value

FROM Tank1Level

WHERE (("Timestamp" > ‘2008-04-01 13:00:00’) AND

 ("Timestamp" < ‘2008-04-01 14:00:00’));

 

Example 4:

To retrieve a list of all active alarms:

 

SELECT Name, Priority

FROM   :Alarms

WHERE  Active = 1

 

Example 5:

To retrieve the names of all disabled alarms in the area "MyArea":

 

SELECT  Name

FROM    :Alarms

WHERE   Disabled = 1 and Area = "MyArea"

 

Example 6:

To retrieve all events associated with the operator, Bob, on April 10, 2008

 

SELECT  Timestamp, Name, SubName, Event

FROM   :AlarmHistory

WHERE  Timestamp >= '2008-04-10 0:00:00' AND Timestamp < '2008-04-11 0:00:00'

       AND Operator = "Bob"