Compose SQL queries

Filter data using an SQL query to display specific data from the database.
Compose a query:
  • As text
  • By using the String formatter and its relative placeholders:
    sql_identifier
    and
    sql_literal
    .
NOTE: You should not specify the table name in the
FROM
clause. Instead, specify the table name in the dynamic link to the
BrowseName
of the table with an
{0:sql_identifier}
placeholder in a string formatter.

Use case: filter by time

Use the
Time
or
Timestamp
column that contains the date and time to filter data by time.
Use the
sql_literal
literal in the placeholder of the string formatter.
Rockwell Automation
recommends using a temporary
DateTime
variable.
Example. Export records that have a Timestamp greater than a certain instant of time:
SELECT * FROM {0:sql_identifier} WHERE Timestamp > {1:sql_literal}
Example. Export records that have a Timestamp between two instants of time:
SELECT * FROM {0:sql_identifier} WHERE Timestamp BETWEEN {1:sql_literal} AND {2:sql_literal}
TIP: Use the Timestamp column to create a time filter on a Logger. Use the Time column to filter alarm history.

Use case: filter by alarm severity

The
Severity
column contains the value of the severity of an alarm. Use this column to filter various severities of an alarm grid or alarm history grid.
Example. Export the history of alarms with severity 1:
SELECT * FROM {0:sql_identifier} WHERE Severity = 1
Example. Export the history of alarms with severity between 1 and 3:
SELECT * FROM {0:sql_identifier} WHERE Severity BETWEEN 1 AND 3

Use case: filter by alarm or variable

The
ConditionName
column contains the name of the alarm. Filter archived alarms to obtain specific alarms. You can filter alarm names if they follow a specific pattern.
The condition can be used on the
SourceName
column to filter by the input variable.
Example. Export the alarm history with a given BrowseName in the past:
SELECT * FROM {0:sql_identifier} WHERE ConditionName = {1:sql_literal}
Example. Export the history of the alarms connected to the variable:
SELECT * FROM {0:sql_identifier} WHERE SourceName = {1:sql_literal}
Example. Export the history of alarms starting with Exclusive:
SELECT * FROM {0:sql_identifier} WHERE ConditionName LIKE 'Exclusive%'

Use case: filtering Recorded Alarms

Export some columns of the logger through the
SELECT
statement.
Example. Export the alarm history with only the Italian columns:
SELECT "ActiveState_it-IT", "AckedState_it-IT", "ConfirmedState_it-IT", "ConditionName", "EnabledState_it-IT", "SourceName", "Time", "Message_it-IT", "Severity" FROM {0:sql_identifier}
Provide Feedback
Have questions or feedback about this documentation? Please submit your feedback here.