Lists Guide - Use SQL to Create a Filter for a List other than an Alarms List (Display and Use Lists)

Use SQL to Create a Filter for a List other than an Alarms List

When displaying a List such as an Events List, Alarm Summary, Queries List, or Historic List, you can use the Filter Columns window to apply the filter criteria (see Filter a List). When using the Filter Columns window, you can choose to apply the filter settings by using the various combo boxes and fields, or you can select the Advanced button. This button provides access to the SQL text entry field, enabling you to define the filter criteria as an SQL query WHERE Clause. (To revert back to using simple filters, select the Simple button on the Filter Columns window.)

Example:

The Display Points option is used to display a Queries List of the points that are associated with a particular outstation. The Filter option is used to display the Filter Columns window. A further filter is added to restrict the List to only those points that contain the word ‘Sensor’ within their name.

The Filter Columns window indicates the filters that are being used for the List:

The Advanced button is selected on the Filter Columns window. This displays the WHERE Clause—the part of the SQL query that is being used for the filters shown above:

By defining the filter using SQL, you can create more complex filter criteria. To do this, you need a good understanding of the SQL language.

Example:

With the above List, the WHERE Clause is expanded to enable the List to contain points that include either ‘Sensor’ or ‘Switch’ in their names:

( "SourceName" = 'Eastern Region.Zone 1.Direct Outstation' ) AND (( "Name" LIKE '%Sensor%' ) OR ( "Name" LIKE '%Switch%' ) )

Be aware that if a List is filtered using a complex WHERE Clause, only the OK, Cancel and Simple buttons remain available for use on the Filter Columns window.

Selecting the Simple button shows the query in plain language:

To further adjust the filter, specify the required WHERE Clause in the Filter Columns window, or right-click on the List and use the Reset Query option to reset the filter back to the default (see Reset the Filters and Sort Order on a List other than an Alarms List).

Be aware that you can also temporarily control exactly which columns are included in a List, along with the List’s sort order, and the filter criteria, by displaying the SQL query for the whole List (rather than just the SQL for the filters)—for more information, see Ascertain which SQL Query is Presently used by a List. You need a good understanding of the SQL language in order to specify the required SQL query successfully.

Be aware that there are several database fields that perform special functions in Geo SCADA Expert—these fields are not displayed as columns in the List, but do affect the appearance and functionality of the List. For more information, see Fields that Perform Special Functions.

Further Information

Using SQL to Customize Lists.