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
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 theSELECTstatement to select data from a database. This statement supports both column names and value expressions, as well as theALLandDISTINCTset 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 Table1NOTE: TheCASEandWHENexpressions are not supported.IMPORTANT:The SQL standard does not allow combining*with derived columns.SELECT *, Timestamp FROM Table1SELECT *, ROW_NUMBER() OVER (ORDER BY Timestamp) FROM DataLoggerFactoryTalk Optixsupports this standard to allow any query manipulation for Sparkline charts.
- Use theDELETEstatement 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 theUPDATEstatement 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 theCREATEstatement to create tables or tables in databases. This statement partially supportsTABLEandINDEX.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 > 5CREATE TEMPORARY TABLE Table2 AS SELECT Column1 FROM Table1 WHERE Column1 > 5 CREATE UNIQUE INDEX Column1_Index ON Table1 (Column1) DROP TABLE Table1
- TheDROPstatement drops any existing databases or tables in databases.Examples:DROP TABLE TemporaryTable DROP TABLE Table1
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 |
| 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 |
| Do not use spaces. Start with an uppercase or a lowercase letter. You can use numeric digits, letters, and the underscore character _ . |
Delimited identifier |
| 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.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
Operator | Example |
---|---|
IN |
|
BETWEEN |
|
LIKE |
|
EXISTS |
|
IS |
|
NOT |
The NOT operator applies to all other operators. |
NOTE:
ANY
and ALL
operators are not supported.Window functions
Function | Example |
---|---|
ROW_NUMBER |
|
RANK |
|
DENSE_RANK |
|
AVG |
|
SUM , MIN , MAX |
|
COUNT |
|
NOTE:
The
PARTITION BY
clause supports only column identifiers.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.sql
query
Provide Feedback