Operators
This topic explains the operators that you can use in SQL queries in ClearSCADA.
The values or variables on which you use the operators to perform operations are known as 'operands'.
| Operator | Symbol | Description |
Precedence Level (1= highest) |
|---|---|---|---|
| IN | IN |
This logical operator enables you to compare a value to a list of values in a WHERE clause. You specify the literal values in a comma-separated list that you enclose in parentheses. Example:
SELECT <field> For more information, see Conditions.bool-primary-in. |
1 |
| WITHIN | WITHIN |
Use this boolean operator to determine whether a location is within a region. The format is as follows: (Location WITHIN Region) Both the Location and Region can be either a numeric literal (that is, a set of coordinates) or a database object. Example: SELECT CDBObject.FullName FROM CDBObject LEFT JOIN CGISREGION ON (CGISREGION.Name = 'Regions.West') WHERE (CDBObject.GISLocation->GEOPosition WITHIN CGISRegion.RegionType->GeoRegion) |
1 |
| Negation (numerical) | - |
Returns the negative form of a numeric value. Use this operator with numeric values. Negation is a unary operator, whereby both operands have to be of the same data type. |
1 |
| Negation (logical) | NOT |
Use this operator to reverse (negate) a single condition, so that where the condtion would return TRUE, the NOT condition returns FALSE, and vice versa. Use this operator with Boolean values. Do not confuse the NOT operator with the NOT condition. You can use the NOT condition to inverse the logic of an SQL condition. For example, NOT EXISTS, NOT BETWEEN, NOT IN, and so on. For more information about the NOT condition, see Conditions. |
1 |
| De-reference | -> |
This operator is specific to ClearSCADA. Use the operator to retrieve the value from a reference field, or access any field on an aggregate. For more information, see de-reference operator. |
1 |
| Multiplication | * |
Multiplies one value by another value. Use this operator with numeric and/or interval values. For more information, see Expressions.expr-term. |
2 |
| Division | / |
Divides the left-hand operand (value) by the right-hand operand (value). Use this operator with numeric and/or interval values. For more information, see Expressions.expr-term. |
2 |
| Concatenation | || |
Joins two string or bit string values together. For more information, see Expressions.expr-term. |
2 |
| Addition | + |
Adds one value to another value. Use this operator with numeric, interval, and/or TimeDate values. For more information, see Expressions.expression. |
3 |
| Subtraction | - |
Subtracts the right-hand operand (value) from the left-hand operand (value). Use this operator with numeric, interval, and/or TimeDate values. For more information, see Expressions.expression. |
3 |
| Equality | = |
Compares one value with another to see if they are equal. You can use this operator with any supported data type. For more information, see Conditions.bool-primary-comparison. |
4 |
| Inequality | <> |
Compares one value with another to see if they are inequal. You can use this operator with any supported data type. For more information, see Conditions.bool-primary-comparison. |
4 |
| Less than | < |
Compares one value with another to see if the first value is less than the second value. You can use this operator with any supported data type. For more information, see Conditions.bool-primary-comparison. |
4 |
| Greater than | > |
Compares one value with another to see if the first value is greater than the second value. You can use this operator with any supported data type. For more information, see Conditions.bool-primary-comparison. |
4 |
| Less than or equal to | <= |
Compares one value with another to see if the first value is less than or equal to the second value. You can use this operator with any supported data type. For more information, see Conditions.bool-primary-comparison. |
4 |
| Greater than or equal to | >= |
Compares one value with another to see if the first value is greater than or equal to the second value. You can use this operator with any supported data type. For more information, see Conditions.bool-primary-comparison. |
4 |
The Precedence Level indicates the order in which operations are performed in SQL queries. Highest precedence operations are performed first, lowest precedence operations are performed last. When multiple operators have equal precedence, the operations are performed in the order they appear in the SQL query, from left to right.
Operations within parentheses ( ) are performed before their resultant value is used in the rest of the query.