Expressions
Expressions form part of the On Key Query Language (OKQL) and are used by various areas of On Key to support different features.
Some of the areas using expressions are:
- On Key REST API - expressions are used to filter the data returned by API responses.
- On Key Front-end - expressions are used to setup conditional formatting rules.
- On Key Back-end - expressions are used to create Change Data Triggers to get notified of data changes.
- On Key Asset Register - expressions are used within rules to support the creation of dynamic Asset Management Plans.
Expressions use property identifiers to uniquely identify and express relationships between resources and their data. Property identifiers are specified using either Property Name or Property Path syntax.
Tip
Please familiarize yourself with property identifiers before continuing any further.
Case Insensitive
OKQL is a case-insensitive language. Any language keyword used in an expression can be specified using mixed casing as illustrated in the following equivalent expression:
datediff(utcnow(), createdOn) le timespan'365.00:00:00'
DateDiff(UTCNOW(), createdOn) LE TiMeSpaN'365.00:00:00'
The case-insensitivity nature of OKQL only applies to the grammar elements of the expression and not to how data is compared using the string data types and string comparison operators. The following expressions are therefore not equivalent:
createdByUserFullName eq 'Luke'
createdByUserFullName eq 'LUKE'
Data Types
The following data types are supported in expressions:
Text
Either a single quote (') or a double quote (") can be used as STRING delimiters.
| Type | Purpose | Examples | Limitations |
|---|---|---|---|
| STRING | Text | 'Filler Machine' or "Filler Machine" |
Special characters need to be escaped |
Special Characters
When using special characters within STRING literals, the special characters need to be escaped. Special characters can be escaped by either prefixing the character with a backslash symbol (\) or by using the Unicode escape format of \uxxxx where xxxx is the hexadecimal Unicode value for the special character.
Here are some examples to illustrate how to escape some STRING literals.
| Text | Single Quote | Double Quote |
|---|---|---|
Window can't close |
'Window can\'t close' |
"Window can't close" |
Window can't close |
'Window can\u0027t close' |
"Window can't close" |
Window title "About" |
'Window title "About"' |
"Window title \"About\"" |
Window title "About" |
'Window title "About"' |
"Window title \u0022About\u0022" |
In addition to using their Unicode values, the following list of control characters are also directly supported in STRING literals:
| Control Character | Text |
|---|---|
| Backspace | \b |
| Horizontal Tab | \t |
| Line feed | \n |
| Carriage return | \r |
| Form feed | \f |
Numerical Types
| Type | Purpose | Literal | Examples | Limitations |
|---|---|---|---|---|
| BYTE | Signed 8 bit whole Number | 0x |
0x20, -0x20 |
2^8-1 |
| INT | Signed 32 bit whole Number | 20, -20 |
2^32-1 | |
| LONG | Signed 64 bit whole Number | L |
20L, -20L |
2^64-1 |
| SINGLE | Floating point Number | f |
20.0053f, -20.0053f |
-3.4 × 1038 to +3.4 × 1038 |
| DOUBLE | Floating point Number | d |
20.0053d, -20.0053d |
±5.0 × 10−324 to ±1.7 × 10308 |
| DECIMAL | Decimal floating point Number | m |
20.50m or 20.50, -20.50m or -20.50 |
(-7.9 x 1028 to 7.9 x 1028) / (100 to 1028) |
Date/Time Types
| Type | Purpose | Examples | Limitations |
|---|---|---|---|
| TIMESPAN | Time period | timespan'2.12:00:00' |
timespan'[-][d.dddd]hh:mm:ss[f.fffffff]' |
| DATETIME | ISO 8601 Date with time | datetime'2017-09-01T14:35:00' |
datetime'yyyy'-'MM'-'dd'[T'HH':'mm'[:'ss'.'fffffff]]' with the time segment, or the seconds and fractional seconds of the time segment, being optional. If no time segment is specified, Specifying time offsets from UTC is not supported and the value specified is always assumed to be UTC time. |
Miscellaneous Types
| Type | Purpose | Examples | Limitations |
|---|---|---|---|
| BOOL | Flag | TRUE, false |
TRUE or FALSE |
| NULL | Null literal | NULL |
NULL or null |
| GUID | Guid literal | guid'8c238865-a3fc-43ec-996b-560fa774fc55' |
Logical operators
The following logical operators are supported in expressions. Expressions using these operators can be nested and grouped using ( and ) brackets.
| Operator | Purpose | Example | Limitations |
|---|---|---|---|
and or AND |
Logical AND | a and b |
Conditional expression |
or or OR |
Logical OR | a or b |
Conditional expression |
not or NOT |
Logical NOT | not (a or b) |
Conditional expression |
Comparison operators
The following comparison operators are supported in expressions:
| Operator | Purpose | Example | Limitations |
|---|---|---|---|
gt or GT or > |
Greater Than | a gt b or a GT b or a > b |
Numeric and DateTime data types |
ge or GE or >= |
Greater Than Or Equal | a ge b or a GE b or a >= b |
Numeric and DateTime data types |
lt or LT or < |
Less Than | a lt b or a LT b or a < b |
Numeric and DataTime data types |
le or LE or <= |
Less Than Or Equal | a le b or a LE b or a <= b |
Numeric and DateTime data types |
eq or EQ or = |
Equal | a eq b or a EQ b or a = b |
|
ne or NE or <> |
Not Equal | a ne b or a NE b or a <> b |
|
startswith or STARTSWITH |
Text starts with | a startswith 'b' |
Text data types only |
endswith or ENDSWITH |
Text ends with | a endswith 'b' |
Text data types only |
like or LIKE |
Text contains | a like 'b' |
Text data types only |
in or IN |
Value equals to any of | year in (1900,1950,2000) or 'TaskMustBeInitialised' in schedulingFailReasons |
All elements in the list need to be compatible data types |
contains or CONTAINS |
List value contains all of | schedulingFailReasons contains ('TaskMustBeInitialised','TaskIsPausedForScheduling') |
Only supported for arrays. All elements in the list need to be compatible data types |
Mathematical operators
The following mathematical operators are supported in expressions. Expressions using these operators can be nested and grouped using ( and ) brackets.
| Operator | Purpose | Example | Limitations |
|---|---|---|---|
+ or - |
Add or subtract | year(Asset->CreatedOn) + 2 |
Numerical and timespan data types |
* or / or % |
Multiply, divide or modulos | Asset->DepreciationPercentage * 100 |
Numerical data types |
- |
Negate | -1567/200 |
Numerical and timespan data types |
Functions
The OKQL supports a wide array of functions to further enhance the capabilities of expressions. Functions can invoke other functions and provide a lot of flexibility in creating more complex expressions.
Text functions
The following list of functions can be used on STRING data types:
| String Functions | Purpose | Example |
|---|---|---|
tolower ( STRING text ) : STRING |
Lower case text |
tolower('ABC') = 'abc' |
toupper ( STRING text ) : STRING |
Upper case text |
toupper('abc') = 'ABC' |
ltrim ( STRING text, STRING chars ) : STRING |
Left trim text by removing chars. |
ltrim('00123','0') = '123' |
rtrim ( STRING text, STRING chars ) : STRING |
Right trim text by removing chars |
rtrim('12300','0') = '123' |
trim ( STRING text, STRING chars ) : STRING |
Left and right trim text by removing chars |
trim('01230','0') = '123' |
lpad ( STRING text, INT length, STRING chars ) : STRING |
Left pad text by adding chars to produce a string with total length of characters |
lpad('ABC',5,'0') = '00ABC' |
rpad ( STRING text, INT length, STRING chars ) : STRING |
Right pad text by adding chars to produce a string with total length of characters |
rpad('ABC,5,'0') = 'ABC00' |
substring ( STRING text, INT startAt, INT length ) : STRING |
Returns a substring from text, starting at startAt for length characters |
substring('Hello world',0,5) = 'Hello' |
concat ( STRING[] segments ) : STRING |
Returns a concatenated string by appending all the comma separated segments to text |
concat('Hello','World') = 'HelloWorld' |
indexof ( STRING chars, STRING text ) : INT |
Returns the lowest index at which chars appears within text or -1 otherwise |
indexof('He','Hello world') = 0 |
Date/Time functions
The following list of functions can be used on DATETIME and TIMESPAN data types:
| Date Functions | Purpose | Example |
|---|---|---|
utcnow ( [TIMESPAN offset] ) : DATETIME |
Returns the current date time in UTC format adjusted with an optional offset |
utcnow() = datetime'2017-09-01T14:35:00' |
datemod ( DATETIME date, TIMESPAN offset ) : DATETIME |
Returns a modified date by adding the offset |
datemod(datetime'2017-09-01T14:35:00, timespan'01:00:00') = datetime'2017-09-01T15:35:00' |
datediff ( DATETIME date1, DATETIME date2 ) : TIMESPAN |
Returns a timespan that represents the difference between date1 and date2 |
datediff(datetime'2017-09-01T14:35:00', datetime'2017-09-01T12:35:00') = timespan'02:00:00' |
year ( DATETIME date ) : INT |
Returns the calendar year for date |
year(datetime'2017-09-01T14:35:00') = 2017 |
month ( DATETIME date ) : INT |
Returns the calendar month for date |
month(datetime'2017-09-01T14:35:00') = 9 |
day ( DATETIME date ) : INT |
Returns the calendar day for date |
day(datetime'2017-09-01T14:35:00') = 1 |
hour ( DATETIME date ) : INT |
Returns the 24 hour clock value for date |
hour(datetime'2017-09-01T14:35:00') = 14 |
minute ( DATETIME date ) : INT |
Returns the minute clock value for date |
minute(datetime'2017-09-01T14:35:00')' = 35 |
second ( DATETIME date ) : INT |
Returns the second clock value for date |
second(datetime'2017-09-01T14:35:10') = 10 |
Mathematical functions
The following list of functions can be used on NUMERIC (e.g. INT,LONG,SINGLE,DOUBLE,DECIMAL) data types:
General functions
| Math Functions | Purpose | Example |
|---|---|---|
abs ( NUMERIC number ) : NUMERIC |
Returns the absolute value of the specified number |
abs(-10.50) = 10.50 |
pow ( NUMERIC number, NUMERIC exponent ) : NUMERIC |
Returns the number raised to the specified exponent |
pow(2, 3) = 8 |
pi() : DOUBLE |
Represents the ratio of the circumference of a circle to its diameter. | pi() = 3.14159265358979 |
exp ( NUMERIC power ) : DOUBLE |
Returns e raised to the specified power |
exp(2) = 7.38905609893065 |
sqrt ( NUMERIC number ) : DOUBLE |
Returns the square root of the specified number |
sqrt(49) = 7 |
ln ( NUMERIC number ) : DOUBLE |
Returns the natural logarithm of the specified number (to base e) |
ln(2) = 0.693147180559945 |
logn ( NUMERIC base, NUMERIC number ) : DOUBLE |
Returns the logarithm of the number to the specified base |
log(2,100) = 6.64385618977 |
sign ( NUMERIC number ) : DOUBLE |
Returns the sign (-1,0,1) of the specified number |
sign(-8) = -1 |
Rounding functions
| Math Functions | Purpose | Example |
|---|---|---|
floor ( NUMERIC number ) : NUMERIC |
Returns the largest integer less than or equal to the specified double-precision floating-point number |
floor(20.6) = 20 |
ceiling ( NUMERIC number ) : NUMERIC |
Returns the smallest integer greater than or equal to the specified number. |
ceiling(20.4) = 21 |
round ( NUMERIC number ) : NUMERIC |
Rounds the number to the nearest integer. |
round(20.5) = 21, round(20.4) = 20 |
round ( NUMERIC number, INT places ) : NUMERIC |
Rounds the number to the specified places of fractional digits. |
round(10.125,2) = 10.13 |
trunc ( NUMERIC number ) : NUMERIC |
Removes the fraction of number and returns the resulting integer. |
trunc(10.9) = 10 |
Trigonometric functions
| Math Functions | Purpose | Example |
|---|---|---|
sin ( NUMERIC angle ) : DOUBLE |
Returns the sine of the specified angle measured in radians | sin(2) = 0.909297426825682 |
cos ( NUMERIC angle ) : DOUBLE |
Returns the cosine of the specified angle measured in radians | cos(2) = -0.416146836547142 |
tan ( NUMERIC angle ) : DOUBLE |
Returns the tangent of the specified angle measured in radians | tan(2) = -2.18503986326152 |
asin ( NUMERIC number ) : DOUBLE |
Returns the angle whose sine is the specified number | asin(0.5) = 0.523598775598299 |
acos ( NUMERIC number ) : DOUBLE |
Returns the angle whose cosine is the specified number | acos(0.5) = 1.0471975511966 |
atan ( NUMERIC number ) : DOUBLE |
Returns the angle whose tangent is the specified number | atan(0.5) = 0.463647609000806 |
atan2 ( NUMERIC number1, NUMERIC number2 ) : DOUBLE |
Returns the angle whose tangent is the quotient of the two specified numbers | atan2(0.5, 0.5) = 0.785398163397448 |
Conversion functions
| Math Functions | Purpose | Example |
|---|---|---|
degrees ( NUMERIC radians ) : DOUBLE |
Returns the radians converted to degrees |
degrees(0.5) = 28.64788975654116 |
radians ( NUMERIC degrees ) : DOUBLE |
Returns the degrees converted to radians |
radians(45.0) = 0.7853981633974483 |
cast ( NUMERIC number, NUMERIC_TYPE type ) : NUMERIC |
Returns the number converted to the numeric type (int, long,decimal,single or double) |
cast(45.0, int) = 45 |
Specialised functions
In addition to the list of standard functions mentioned above, different service groupings/modules within On Key have some specialized functions which can be used to return specific values/aggregated values for the relevant resource:
| Module | Resource | Function | Purpose | Example |
|---|---|---|---|---|
| IDENTITY API | My Identity |
my_id() : LONG |
Returns the id for the current authenticated user | WorkOrder->ModifiedByUser_Id eq my_id() |
| AHM API | Monitoring Point |
avg_monitoring_point_reading ( LONG monitoringPointId, TIMESPAN interval ) : DECIMAL |
Returns the average monitoring point reading value for an interval |
avg_monitoring_point_reading( 10003438, timespan'02:00:00' ) = 200.123 |
| AHM API | Monitoring Point |
count_monitoring_point_reading ( LONG monitoringPointId, TIMESPAN interval ) : LONG |
Returns the number of monitoring point reading values for an interval |
count_monitoring_point_reading( 10003438, timespan'02:00:00' ) = 2000 |
| AHM API | Monitoring Point |
min_monitoring_point_reading ( LONG monitoringPointId, TIMESPAN interval ) : DECIMAL |
Returns the minimum monitoring point reading value for an interval |
min_monitoring_point_reading( 10003438, timespan'02:00:00' ) = 200.12 |
| AHM API | Monitoring Point |
max_monitoring_point_reading ( LONG monitoringPointId, TIMESPAN interval ) : DECIMAL |
Returns the maximum monitoring point reading value for an interval |
max_monitoring_point_reading( 10003438, timespan'02:00:00' ) = 200.123 |
| AHM API | Monitoring Point |
monitoring_point_reading ( LONG monitoringPointId, [INT offset] ) : DECIMAL |
Returns the monitoring point reading value at the specified offset in the past. If no offset is provided, it returns the latest available reading |
monitoring_point_reading( 10003438 ) = 200.123 |
| AHM API | Monitoring Point |
monitoring_point_readingon ( LONG monitoringPointId, [INT offset] ) : DATETIME |
Returns the monitoring point reading on date at the specified offset in the past. If no offset is provided, it returns the latest available reading on date |
monitoring_point_readingon( 10003438 ) = datetime'2018-01-01:12.05:23 |
Data lookup
In addition to the above-mentioned functions, a specialized datalookup function is available to load a single value for a resource by doing a datalookup with a user supplied value/parameter. To illustrate, consider the following example where we use a datalookup within a expression to find the Asset records that were modified after a specific user supplied Asset was created:
Asset->ModifiedOn gt datalookup(Asset->Id,@assetId,Asset->CreatedOn)
A datalookup consists out of:
datalookup( IDENTIFIER key , LITERAL/PARAMREF keyValue, IDENTIFIER output, IDENTIFIER[] orderby )
| Element | Description |
|---|---|
key |
Property identifier for the key of the resource property |
keyValue |
Value or parameter reference to supply the value for the key |
output |
Property identifier to the resource property to return |
orderby |
Optional list of comma separated property identifiers. If provided, the data output will be sorted using the orderby and only the first item in the list will be returned as the output value |