An SQL query is used to define the entries in Lists other than Alarms Lists. To display the SQL query that is presently used by a List:
- Display the List in ViewX (see Display a List).
- Right-click on the List.
A context-sensitive menu is displayed. - Select the Edit Query option.
The SQL query that is used for that particular query is displayed in an SQL window.
(To display the SQL query that is presently used by a List that is embedded on a Mimic, ensure that the Mimic is being displayed in Design mode (see Displaying a Mimic in the ClearSCADA Guide to Mimics). Double-click on the List to display the Embedded List Properties window, then select the Query tab to view the SQL query that is being used for that List.)
Example:
The Display Points pick action is used to display a List of the points that are associated with a particular DNP3 outstation.
The SQL window is displayed in order to ascertain the SQL query that is being used to display the entries in that particular List.
The SQL window shows that the SQL query returns a series of fields (listed below the SELECT clause) from the ClearSCADA Point Table CDBPoint. The SQL retrieves only those records that pertain to the database item ‘Direct Outstation 1’ within the ‘DNP3 Direct’ Group, which is itself in the ‘DNP3 Group’ (as defined by the WHERE clause). Finally, the returned data is displayed in ascending order (ASC), by full name.
(The Id, Foreground, Blink, and Background fields are ‘special’ fields that are not presented as columns in the List.)
The WHERE Clause is the SQL that is used for the List’s filters—this can also be determined by right-clicking on the List and selecting the Filter option to display the Filter Columns window:
(You can select the Advanced button on the Filter Columns window to show the actual WHERE Clause, and edit that clause if required (see Use SQL to Create a Filter for a List other than an Alarms List).)
The ORDER BY Clause is the SQL that is used to determine the List’s sort order—this can also be determined by right-clicking on the List and selecting the Sort option to display the Sort Order window:
Be aware that if you change the SQL on a List that is not embedded in a Mimic, any changes will be lost as soon as that List is closed.
Further Information
SQL Query Structure in the ClearSCADA Guide to SQL.