SQL queries

SQL (Structured Query Language) manages data stored in relational databases through simple, declarative statements.

SQL grammar

The

FactoryTalk Optix

SQL parser is based on a simplified version of standard SQL 2003 grammar. Specific dialects such as platform-specific commands for MySQL, SQL Server, or InfluxDB are not supported.

TIP:

To leverage advanced queries or stored procedures, consider using the Microsoft.Data.SqlClient NuGet package in custom NetLogics.

Rockwell Automation

is not responsible for any third-party software, including but not limited to its performance, security, updates, or compatibility with our products. Any use of third-party software is at your own risk, and we do not provide support, warranties, or guarantees for such software. Users should refer to the respective third-party providers for assistance and licensing terms.

For more information, see NetLogic.

Statements

TIP: Escape column names with special characters in this way:

SELECT MAX("/Variable1"
) FROM mystore.recipeschema1;

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

CREATE TEMPORARY TABLE "##TempTable" AS SELECT Column1 FROM MyTable WHERE Column1 > 5

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

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 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.

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.

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

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

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

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

regular expression.

Provide Feedback

Have questions or feedback about this documentation? Please submit your feedback here.