SQL: Reference and Examples
Read the following warnings before writing your queries. These may affect your ability to obtain the results you want.
Limit on result set size
The size of the result set is limited by the application property, SQLQueryMaxResultRows and SQLQueryMaxResultCells. SQLQueryMaxResultRows defaults to 10000 and SQLQueryMaxResultCells defaults to 1000000. The size of the result set is calculated using the following rules:
- The number of rows that will be returned is the smaller one between SQLQueryMaxResultRows and the number calculated by SQLQueryMaxResultCells.
- When calculating the number of columns in the query, all columns in the WHERE and ORDER BY clause are included. The Timestamp column is excluded.
If you are changing SQLQueryMaxResultRows and SQLQueryMaxResultCells to be higher than their default values and are using a WHERE clause that filters on something other than time ranges, you may also need to increase SQLQueryMaxResultRowsPreFilter and SQLQueryMaxResultCellsPreFilter.
- SQLQueryMaxResultRowsPreFilter specifies the maximum number of records that can exist in memory before the WHERE filter is applied. Defaults to 1000000.
- SQLQueryMaxResultCellsPreFilter specifies the maximum number of the cells, which is the number of rows times the number of columns, that can exist in memory before the WHERE filter is applied. Defaults to 10000000.
You are strongly advised to use a time range having both a start date and end date in every query. Doing otherwise may take excessive time and return an extremely large data set.
The property, SQLQueryParserDepthLimit, limits how deeply nested a recursive query can be. You may increase this value above the default of 300 but do so with caution. An high number of recursions can consume resources to the point of causing a crash.
Limit on the number of columns returned
A maximum of 64 columns will be returned in any query. This cannot be changed.
All data is stored with UTC timestamps
Your query must adjust for local time. If you are located in GMT -4 then data recorded at 8am in your local time zone (08:00:00) was stored as being recorded at noon GMT (12:00:00).
Results are sorted based on ASCII value
As opposed to alphabetically or lexicographically. The results may resemble alphabetical order with some differences such as uppercase and lowercase being ranked separately.
Table names with spaces, etc.
Wrap table names in single quotation marks so that full tag names, which may include spaces and backslashes, are treated as a single name. For example: SELECT 'Station 1\PLC1\Level:value', Timestamp FROM History ORDER BY Timestamp DESC LIMIT 10
History_TPP tables will not report an error if queried for a time span that extends into the future.
If querying a History_TPP table with a time span that extends into the future, the last known value will be carried forward.
To prevent free browsing of the main history tables, consider using one or more of the SQL View Tag, each of which can be assigned a custom privilege. In addition, deny access to the Remote Tag Value / History Retrieve privilege so that accounts with Remote Data Access can view only their assigned SQL views.
GET queries
When querying a relational database using SQL, all queries begin with SELECT. But you are querying VTScada's proprietary data storage system, using an interface that simulates a subset of SQL. In addition to SELECT, the following queries are supported:
Returns all table names in the data store.
Returns a description of each column of the specified table.
Returns the primary key of the specified table.
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], 'columnspecifier-2'...]
[LIMIT [offset, ] row_count]
For the above ...
- Where the column specifier refers to a tag's value, it must include the full tag name with the suffix, ":value".
- In the alarm tables and the TPP tables, you may use a * to indicate all columns, but you cannot use it as a wildcard to select columns having a common root.
- Column name aliases may be used in the SELECT clause to make the result more readable, but they are not supported in the WHERE clause. All column names in WHERE clauses must be fully specified.
- 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 a space or colon.
- When a table-alias name is specified it may be used in place of the table-name in the column specifier. Table names are only required in the column specifier when querying multiple tables.
- The where-expression filters the data that will be retrieved by the query. Refer to the following table for functions and operators that may be used in the WHERE expression.
- Use the limit clause to restrict the number of rows that will be returned, and optionally add an offset (0-based), which allows pagination. LIMIT is applied after WHERE filtering and after sorting. If there is no explicit LIMIT clause in the query there is an implicit "LIMIT SQLQueryMaxResultRows" (Settings.Dynamic setting) added.
When more than one table is specified, the tables are automatically joined based on their Timestamp columns and must have matching time periods.
A JOIN expression may be used in place of a table list, but it is only parsed to extract the tables specified. VTScada's implementation of SQL does not support JOIN operations.
Functions, Operators and Expressions
Only WHERE clauses may use functions, operators and expressions. In the SELECT clause, mathematic operators will be ignored and functions will return a "column not found" error.
Entries in the following table are grouped in order of decreasing precedence, where entries in each group have the same level.
| Operator, function or keyword | Description |
|---|---|
| First Level | |
| () | Use parenthesis to enclose the parameters of a function and to group expressions, thereby controlling the precedence of operations. |
| Constant (including sign) | Any constant value to which you are comparing a field. |
| Column Reference |
Tag name and logged property. WHERE 'Tank1Level:Value' > 10 |
| Second Level | |
| ABS | Absolute value function. |
| CASE | The CASE statement is a standard SQL command, but in VTScada only the following form is supported: CASE WHEN condition THEN result1 ELSE result2 END |
| CONCAT |
String concatenation. Limited to two parameters. WHERE CONCAT(Area, Operator) = "Western zoneFred" |
| INTEGER | Function to truncate a numeric value to the integer portion. WHERE INTEGER('TankLevel:Average:Value' / 'TankVolume:Average:Value') > 50 |
| LENGTH |
Function to count the number of characters in a field. WHERE LENGTH(HookPointUnits) > 5 |
| LOWER |
Function to convert a string value to lowercase letters. WHERE LOWER(Area) = "water tower" |
| SQRT |
Function to calculate the square root. |
| UPPER |
Function to convert a string value to uppercase letters. WHERE UPPER(Area) = "WATER TOWER" |
| Unary + and - |
Use the positive or negative sign before the value that is to be incremented or decremented by one. |
| Third Level | |
| * | Multiplication operator. |
| / | Division operator. |
| % | Modulus division operator. |
| Fourth Level | |
| + and - | Regular addition and subtraction operators. |
| Fifth Level of Precedence | |
| > | Greater than comparison operator. |
| >= | Greater than or equal to comparison operator. |
| < | Less than comparison operator. |
| <= | Less than or equal to comparison operator. |
| BETWEEN ... AND ... |
Example: WHERE x BETWEEN y AND z. Functionally equivalent to WHERE x >= y and x < z |
| = | Equivalency comparison operator. Note that any comparison to NULL will return NULL, or false. Use IS NULL or IS NOT NULL instead. |
| != and <> | Not equal comparison operator. |
| IS NULL |
Do a test for a null value.
When communication fails, VTScada will record one null (Invalid) for the tag's value at the time of failure. |
| IS NOT NULL | Test for a value that is not null. WHERE 'Tagname:value' IS NOT NULL |
| LIKE |
Test for a value that includes a wildcard. Wildcards may be either: % (equivalent to the VTScada *) or _ (equivalent to the VTScada ?) |
| Sixth Level | |
| NOT | Negation clause for comparisons. |
| Seventh Level | |
| AND | Both clauses must be true in order for a true response to be true. |
| Eighth Level | |
| OR | Either clause must be true in order for a true response. |
Remember that the Timestamp in all tables is stored using UTC. You will need to adjust the time in your query for your time zone and possibly for daylight savings time.
In legacy tables (created prior to version 11.1) the timestamp column used the server's local time zone.
Example 1:
To retrieve error values from a driver called ModDriver1, recorded during a particular one-minute period and adjusted for UTC time.
SELECT Timestamp, 'ModDriver1:ErrorValue' from History WHERE Timestamp BETWEEN '2014-03-01 16:54:00' AND '2014-03-01 16:55:00'
Example 2:
To retrieve several driver statistics from a driver named ModDriver1, during a day, using a TPP of 1 minute: This query is being run in the Eastern Standard time zone (GMT - 5), during standard time.
SELECT Timestamp, 'ModDriver1:ErrorValue:Average', 'ModDriver1:ErrorValue:ValueAtStart,
'ModDriver1:Quality:Average, 'ModDriver1:Quality:ValueAtStart' from History_1M
WHERE Timestamp BETWEEN '2014-03-01 05:00:00' AND '2014-03-02 05:00:00'
Example 2:
To retrieve weekly minimum values from two Analog Input tags called ai1 and ai2 during November 2013, sorted with most recent values first: (Given a SQLQueryTableTPPs property that includes the 1W flag.) This query is being run in Greenwich, England.
SELECT Timestamp,
'ai1:Value:Minimum' AS ai1Min,
"ai2:Value:Minimum' AS ai2Min FROM History_1W
WHERE Timestamp BETWEEN '2013-11-01' AND '2013-11-30'
ORDER BY 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 Timestamp, Tank1Level:Value
FROM History
WHERE Timestamp BETWEEN '2008-04-01 13:00:00' AND '2008-04-01 14:00:00'
or
SELECT Timestamp, Tank1Level:StorageTimeStamp, Tank1Level:Value
FROM History
WHERE Timestamp BETWEEN '2008-04-01 13:00:00' AND '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. This query is being run in the Eastern Standard time zone (GMT - 5), during standard time.
SELECT Timestamp, Name, SubName, Event
FROM AlarmHistory
WHERE Timestamp >= '2008-04-11 5:00:00' AND Timestamp < '2008-04-12 5:00:00'
AND Operator = "Bob"
Example 7:
To find the names of Analog Status tags with an area beginning with 'Plant', followed by anything.
SELECT Name
FROM Parms_AnalogStatus
WHERE Area LIKE 'Plant%'
Example 8:
To find the names of Analog Status tags with an area beginning with 'Plant ', followed by a single character. (Plant 1, Plant 2, but not Plant1)
SELECT Name
FROM Parms_AnalogStatus
WHERE Area LIKE 'Plant _'
Example 9:
To find the names of Analog Status tags with an area beginning with 'Plant_', followed by anything. The underscore must be escaped so that it is not treated as a wildcard.
SELECT Name
FROM Parms_AnalogStatus
WHERE Area LIKE 'Plant~_%' ESCAPE '~'
