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
| Type | Purpose | Examples | Limitations |
|---|---|---|---|
| STRING | Text | '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. The ' character can also be directly escaped by using a double ''.
Here are some examples to illustrate how to escape the Window can't close STRING literal.
'Window can''t close''Window can\'t close''Window can\u0027t close'
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 | Examples | Limitations |
|---|---|---|---|
| BYTE | Signed 8 bit whole Number | 0x20, -0x20 |
2^8-1 |
| INT | Signed 32 bit whole Number | 20, -20 |
2^32-1 |
| LONG | Signed 64 bit whole Number | 20L, -20L |
2^64-1 |
| SINGLE | Floating point Number | 20.0053f, -20.0053f |
-3.4 × 10^38 to +3.4 × 10^38 |
| DOUBLE | Floating point Number | 20.0053d, -20.0053d |
±5.0 × 10^−324 to ±1.7 × 10^308 |
| DECIMAL | Floating point Number | 20.50m, -20.50m |
(-7.9 x 10^28 to 7.9 x 10^28) / (10^0 to 10^28) |
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 |
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) |
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 |
Rigth 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' |
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 numerical (INT,LONG,FLOAT,DOUBLE,DECIMAL) data types:
| Math Functions | Purpose | Example |
|---|---|---|
floor ( DOUBLE/FLOAT/DECIMAL number ) : LONG |
Returns the largest integer less than or equal to the specified double-precision floating-point number | floor(20.5f) = 20 |
ceiling ( DOUBLE/FLOAT/DECIMAL number ) : LONG |
Returns the smallest integer greater than or equal to the specified number. | ceiling(20.4f) = 21 |
abs ( INT/LONG/DOUBLE/FLOAT/DECIMAL number ) : NUMERIC |
Returns the absolute value of the specified number | abs(-10.50m) = 10.50m |
pow ( INT/LONG/DOUBLE/FLOAT number, INT/LONG/DOUBLE/FLOAT exponent ) : NUMERIC |
Returns a number raised to the specified power | pow(2, 3) = 8 |
round ( DOUBLE/FLOAT/DECIMAL number, INT places ) : NUMERIC |
Rounds a value to the nearest integer or to the specified number of fractional digits. | round(10.125f,2) = 10.13f |
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 ) : DOUBLE |
Returns the average monitoring point reading value for an interval |
avg_monitoring_point_reading( 10003438L, timespan'02:00:00' ) = 200.123f |
| 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( 10003438L, timespan'02:00:00' ) = 2000L |
| AHM API | Monitoring Point |
min_monitoring_point_reading ( LONG monitoringPointId, TIMESPAN interval ) : LONG |
Returns the minimum monitoring point reading value for an interval |
min_monitoring_point_reading( 10003438L, timespan'02:00:00' ) = 200.12f |
| AHM API | Monitoring Point |
max_monitoring_point_reading ( LONG monitoringPointId, TIMESPAN interval ) : DOUBLE |
Returns the maximum monitoring point reading value for an interval |
max_monitoring_point_reading( 10003438L, timespan'02:00:00' ) = 200.123f |
| AHM API | Monitoring Point |
monitoring_point_reading ( LONG monitoringPointId, [INT offset] ) : DOUBLE |
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( 10003438L ) = 200.123f |
| 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( 10003438L ) = 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 |