The engineers on a particular ClearSCADA system need ready access to lists of Out of Service points. Ideally, the entries in each List should be restricted to the points that are associated with the particular database Group from which the List is displayed.

The ClearSCADA Server Configuration Tool is used to add an extra Table Query to the database:

The SQL for the Table Query is as follows:

SELECT

"FullName" AS "~FullName", "Id", "Foreground", "Blink", "Background", "CurrentTime", "SourceName", "Source", "TypeDesc"

FROM

CDBPoint

WHERE

("CurrentQuality" = 28) AND ("~FullName" LIKE CASE WHEN $Id$=0 THEN '%' ELSE '$FullName$.%' END)

ORDER BY

"~FullName" ASC

As with any Queries List, the SELECT Clause lists the data fields that are to be retrieved from the database. These include several Fields that Perform Special Functions, which won’t themselves be displayed as columns in the resulting List.

The FROM Clause defines the database table from which the data is to be retrieved. As the Lists are to display points, the clause specifies the overall points table CDBPoint.

The WHERE Clause is used to restrict the List’s content to the relevant points. In this particular case, the CurrentQuality attribute is used to restrict the List’s entries to those points that are out of service. (28 is the CurrentQuality value that corresponds to the CurrentQualityDesc (current quality description) ‘Out of Service’). Using the current quality value in the WHERE Clause is more efficient that specifying the current quality description, and is also language independent. (To ascertain the CurrentQuality value for ‘Out of Service’ you could, for instance, right-click on an Out of Service point in the OPC Data Bar, then select the Display Values option to display the OPC values for that point. The CurrentQuality value is displayed above the corresponding CurrentQualityDesc string.)

Additionally, the CASE scalar function is used in the WHERE Clause to further restrict the entries to those points that either belong to the Root Group (whenever the List is displayed from the root itself—as specified by $Id$=0), or from a specific Group whenever the List is displayed from another Group. (Again, querying by Root Group ID is more efficient than querying by Root Group name.)

The ORDER BY Clause specifies that the order in which the List’s entries are displayed is initially by ascending FullName order.

The ClearSCADA server is restarted to add the additional Table Query to the database.

Whenever an engineer right-clicks on a Group (including the Root Group) in the Database Bar, the context sensitive menu now includes the custom option ‘Display Out of Service Points’. When this option is selected, a list of Out of Service points for that Group is displayed.

The custom menu option is also available from any other displays, such as Mimics or other Lists, from which users can display a context sensitive menu for a Group item.

Users can Filter, Sort, Quick Filter, and perform other actions from the List in the same way as other Queries Lists.