SQL queries

Use the ANSI SQL-92 standard for querying ODBC databases and embedded databases.

Statements

FactoryTalk Optix Studio
 supports these statements in SQL queries:
  • SELECT
  • DELETE
    TIP: Run a
    DELETE
     query to delete a record. You can perform all other operations by using the OPC UA methods exposed by the same objects. For more information on the SQL statements and clauses, see the SQL standard.
  • UPDATE
FactoryTalk Optix Studio
 partially supports these statements in SQL queries:
  • CREATE
  • DROP

Literal values

Supported literal values
Literal value type
Example
Notes
Integral
1520
Do not use any thousands separators.
Use the Int32 type.
Real
15.025
Use
.
 as the decimal separator.
Use the Double type.
Real scientific
2.7000001072883606E-1
Boolean
  • true
  • false
Use lowercase for Boolean values.
String
'string value'
Use single quotes to delimit text.
Insert a single quote (
'
) in a text string by escaping the single quote with another single quote. For example, to write
'string'
, use two single quotes
''string''
 in the strings.
Date and time
"2004-05-23T14:25:10.487"
Use the ISO8601 format delimited by double quotes.
The date and time notation is non-standard ANSI SQL used to avoid the
DATE
,
TIME
, and
TIMESTAMP
 keywords without ambiguity.
Regular identifier
  • Column1
  • Grüße
Do not use spaces.
Start with an uppercase or a lowercase letter.
You can use numeric digits, letters, and the underscore character
_
.
Delimited identifier
  • "Water level"
  • "SELECT"
Use double quotes (
"
) as the delimiter.
In the identifier, use any character except for the
"
 character.
Any spaces at the end of the identifier are ignored.

Aliases

You can use aliases in the identifiers, derived column subqueries, and tables.
The following are examples of queries with aliases: 
SELECT Timestamp AS T FROM Table1 WHERE ...
SELECT * FROM Table1 AS T WHERE ...
SELECT * FROM (SELECT * FROM Table1) AS SubQuery WHERE ...
CREATE TABLE Table1 AS SELECT Timestamp, Column1 FROM Table2

Operators

Supported operators
Operator
Example
IN
SELECT * FROM Table1 WHERE Column1 IN (10, 20, 30)
BETWEEN
SELECT * FROM Table1 WHERE Column1 BETWEEN 100 AND 200
LIKE
... WHERE column1 LIKE '%a'
... WHERE column1 LIKE '%a%'
... WHERE column1 LIKE '%bbpi!%ppo%' ESCAPE '!'
EXISTS
... WHERE EXISTS (SELECT Table1.Column1 FROM Table2)
IS
... WHERE column1 IS NULL
NOT
... WHERE column1 IS NOT NULL
... WHERE column1 NOT IS (10, 20)
... WHERE column1 NOT BETWEEN 100 AND 200
The
NOT
 operator applies to all other operators.

Window functions

Supported window functions
Function
Example
ROW_NUMBER
ROW_NUMBER() OVER (ORDER BY Column2)
ROW_NUMBER() OVER (PARTITION ON Column1 ORDER BY Column2)
ROW_NUMBER() OVER (window_name PARTITION ON Column1 ORDER BY Column2)
RANK
RANK() OVER (PARTITION ON Column1 ORDER BY Column2)
DENSE_RANK
DENSE_RANK() OVER (PARTITION ON Column1 ORDER BY Column2)
AVG
AVG(Column1) OVER (PARTITION BY Column2)
SUM
,
MIN
,
MAX
SUM(Column1) OVER (PARTITION BY Column2)
COUNT
COUNT(Column1) OVER (PARTITION BY Column2)
COUNT(*) OVER (PARTITION BY Column2)
Provide Feedback
Have questions or feedback about this documentation? Please submit your feedback here.
Normal