de-reference operator
The de-reference operator uses the syntax -> and is specific to ClearSCADA. Use the operator to perform either of the following:
- Extract a value from a reference field (a field that references another item in the database)
- Access any field on an Aggregate (an Aggregate is an extension of a database Class).
You can use the de-reference operator in a SELECT list and/or a WHERE clause.
To reference a database field in SQL, you reference the column that represents that field in the relevant database table. The column reference comprises the data type of the database field that the column represents (see Data Types).
Format
You can use any combination of the following:
reference->ColumnName
aggregate->ColumnName
reference->aggregate->ColumnName
where ColumnName is the name of a field in the database.
Remarks
You can use the de-reference operator to chain reference fields, in the format: reference->reference
You cannot use the de-reference operator to chain aggregates (aggregate->aggregate), as ClearSCADA does not support nested aggregates.
You can chain multiple de-reference operators (taking into account the restriction mentioned above). For example, reference->aggregate->reference->reference->aggregate->ColumnName
You can include multiple de-reference operators in a single query. For example, SELECT aggregate->ColumnName, reference->aggregate->ColumnName WHERE aggregate->ColumnName = value
You can use the de-reference operator to access aggregates without the need for joins. This might result in a faster query, but each operation will incur a database lock while ClearSCADA looks up each database item, aggregate, and column (database field) in each row. As such, we advise that you perform de-reference operations sparingly to avoid potential performance implications.
SELECT
PARENTGROUPID->FULLNAME AS "Location in Database", NAME
FROM
CADVMODBUSANALOG
This SELECT clause extracts the 'Full Name' of the Parent Group of each Advanced Modbus analog point in the database. It does this by using the de-reference operator (->) to access each point's Parent Group via the 'ParentGroupId' reference field. This reference field is a configuration field in the CDBObject class, which is a base class of the CAdvModbusAnalog class.
Rather than display the text 'ParentGroupId' in the column heading when the query is displayed in List format, an alias is used so that the column heading displays the text "Location in Database" instead.
The SELECT clause is also used to display a second column; that showing the name of each Advanced Modbus analog point in the database.
SELECT
GISLOCATION->GEOPOSITION AS "Geographical Location", NAME
FROM
CDBPOINT
This SELECT clause extracts the 'Position' string ('GeoPosition' property) of each point in the database. It does this by using the de-reference operator (->) to access each point's 'Location' aggregate (GISLocation), from which the query extracts the GeoPosition value. GISLocation is an aggregate of the CDBObject class, which is a base class of the CDBPoint class.
Rather than display the text 'GeoPosition' in the column heading when the query is displayed in List format, an alias is used so that the column heading displays the text "Geographical Location" instead.
The SELECT clause is also used to display a second column; that showing the name of each point in the database.
SELECT
GISLOCATION->GEOPOSITION AS "Geographical Location", NAME
FROM
CDBPOINT
WHERE
PARENTGROUPID->FULLNAME LIKE 'Eastern Region.%'
This query uses a WHERE clause to restrict the results to just those points that are in the 'Eastern Region' Group, or one of its sub-Groups. It does this by using a de-reference operator (->) to access each point's Parent Group via the 'ParentGroupId' reference field, and only extract those entries for which the Parent Group's 'Full Name' property includes the text 'Eastern Region'.
When you use the de-reference operator with the WHERE clause you must use " " correctly. The following example illustrates this issue:
The following query will fail:
SELECT
Name
FROM
CGROUP
WHERE
"NAME"<>"
The column alias NAME must be exact. When an alias is not explicitly set, it defaults to the column name. The following query works successfully:
SELECT
NAME
FROM
CGROUP
WHERE
("Name"<>")
This issue is not exclusive to the use of an aggregate, but also to the use of a reference column, for example the following will also fail:
SELECT
ParentGroupId->Name
FROM
Cdbobject
WHERE
("PARENTGROUPID->NAME"<>")
For information about the SELECT clause, see the example above.
Determine the name of a Reference field
You can use the Database Schema to determine the name of a reference field. In the database Schema, fields that form reference fields are assigned the type 'Reference' (or 'AOI Reference' in the case of Area of Interest Reference fields) (see Fields).
Remember that the name that appears alongside a field on a configuration Form in ViewX is that property's display name—the string that is displayed in the language that is associated with the logged on user's specified Locale. However, you can ascertain a field's name by selecting the field on a configuration Form, then looking at the name that is displayed in the Status Bar at the bottom of the ViewX window. For an example that demonstrates this, see Display a List of Property Changes in the ClearSCADA Guide to Core Configuration.
Determine the name of an Aggregate and its fields
You can use the Database Schema to determine the name of an Aggregate. In the database Schema, display the Class for which the aggregate is an extension, and then scroll down the page to locate the names of the Aggregates associated with that class. To determine the name of a field on an aggregate, select the link to the class that is associated with that aggregate (with multiple-class aggregates, there will be several classes).
Aggregates typically appear as tabs on configuration Forms in ViewX. Again, remember that the name of an aggregate on a configuration Form in ViewX is that aggregate's display name—the string that is displayed in the language that is associated with the logged on user's specified Locale. To determine the aggregate's name, you have to use the database Schema. Likewise, the name that appears alongside a field on an aggregate on a configuration Form in ViewX is that property's display name; however you can ascertain the field's name by selecting the field on the configuration Form and looking at the name that is displayed in the Status Bar at the bottom of the ViewX window.
Further Information
Supported data types: see Operands and Resulting Data Types.
Other examples that demonstrate the use of the de-reference operator: see column-ref and see Expressions.expr-column-ref.