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

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}

SQL query

Provide Feedback

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