SQL queries

SQL (Structured Query Language) manages data stored in relational databases through simple, declarative statements. Use the ANSI SQL-92 standard for querying ODBC databases and embedded databases.

SQL grammar

The top-down SQL parser is based on a simplified version of standard SQL grammar because:
  • FactoryTalk Optix supports only a subset of standard SQL.
  • The simplified standard SQL grammar best fits to the top-down parser implementation.
  • The simplified standard SQL grammar facilitates the readability of the resulting AST.

Statements

FactoryTalk Optix Studio supports these statements in SQL queries:

  • Use the SELECT statement to select data from a database. This statement supports both column names and value expressions, as well as the ALL and DISTINCT set quantifiers.

    Examples: 
    SELECT *, FROM Table1
SELECT *, Timestamp AS T FROM Table1
SELECT Column1 FROM Table1
SELECT *, 10 FROM Table1
SELECT 10, * FROM Table1
SELECT *, 'text value' FROM Table1
SELECT COUNT(*) FROM Table1
SELECT DINSTINCT Column1 FROM Table1
    Note: The CASE and WHEN expressions are not supported.
    Important:

    The SQL standard does not allow combining * with derived columns.

    SELECT *, Timestamp FROM Table1

    SELECT *, ROW_NUMBER() OVER (ORDER BY Timestamp) FROM DataLogger

    FactoryTalk Optix supports this standard to allow any query manipulation for Sparkline charts.

  • Use the DELETE statement to delete any existing records in a table.

    Examples: 
    SELECT * FROM Table1 WHERE ...
DELETE FROM Table1 WHERE ...
UPDATE Table1 SET column1 = 10 WHERE column2 > 20 ...

  • Use the UPDATE statement to modify any existing records in a table.

    Examples: 
    SELECT * FROM Table1 WHERE ...
DELETE FROM Table1 WHERE ...
UPDATE Table1 SET column1 = 10 WHERE column2 > 20 ...
FactoryTalk Optix Studio partially supports these statements in SQL queries:

  • Use the CREATE statement to create tables or tables in databases. This statement partially supports TABLE and INDEX.

    Examples: 
    CREATE UNIQUE INDEX Table_Index ON Table1 (Column1)
CREATE INDEX IF NOT EXISTS Table_Index ON Table1 (Column1)
    CREATE TABLE TempTable AS SELECT Column1 FROM MyTable WHERE Column1 > 5
CREATE TEMPORARY TABLE TempTable AS SELECT Column1 FROM MyTable WHERE Column1 > 5
    CREATE TEMPORARY TABLE Table2 AS SELECT Column1 FROM Table1 WHERE Column1 > 5
CREATE UNIQUE INDEX Column1_Index ON Table1 (Column1)
DROP TABLE Table1

  • The DROP statement drops any existing databases or tables in databases.

    Examples: 
    DROP TABLE TemporaryTable
DROP TABLE Table1

Literal values

Table 1. Supported literal values
Literal value type Example Notes

Integral

1520

Do not use 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 theDATE, 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.

Join

Any kind of joins such as INNER, UNION, LEFT, RIGHT, FULL, NATURAL and CROSS is supported.
Note: Some joins are not fully supported by every DBMS.

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

Table references

The FROM clause supports the following table references.

Table 2. Supported table references
Reference Example

Fully qualified reference

... FROM table_name

... FROM schema_name.table_name

... FROM database_name.schema.name.table_name

Sub query

... FROM (SELECT * FROM Table2) WHERE ...
Note: Fully qualified names are also supported as column references.
Examples: 
... WHERE Table1.Column1 > 10... 
WHERE Table1."order id" > 10
SELECT * FROM Table1 AS T WHERE T."order id" > 10
... WHERE "my table"."order id" > 10

Operators

Table 3. 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.
Note: ANY and ALL operators are not supported.

Window functions

Table 4. 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)
Note: The PARTITION BY clause supports only column identifiers.

Tokens

Table 5. Supported tokens
Token Regular expression
regular_identifier [a-zA-Z][a-zA-Z0-9_]*
delimited_identifier ".*?"
real
boolean

Other trivial tokens

Regex for single char or single-word tokens
Note: The lexer does not recognize date and time values as tokens: they are parsed as delimited_identifier. The semantic analyzer extracts any date and time values from the delimited_identifier according to the r regular expression.