OQL Expression Syntax

Introduction

Operators and functions in OQL use expressions as inputs to perform mathematical, comparison, conditional, string, date operations and return the result. They allow an OQL query to perform modifications on data on the database to present a different view of the data or make complex conditions.

This document details the use and syntax of expressions in an OQL query.

Data Types

OQL supports a set of data types that differ slightly from Mendix data types. The supported data types are:

Literals

Literals represent values that are constant and are part of the query itself. The supported literals are detailed below:

Where d is a number, s is any character, * indicates that the pattern can contain zero or more characters, and + indicates that the pattern can contain one or more characters.

DATETIME

There is no direct support for DATETIME literals. For functions that take DATETIME as input, it can be represented with a STRING in a ISO date time format or a LONG value representing Unix seconds.

System Variables

Most XPath system variables can be used in OQL with the format:

'[%SystemVariable%]'

These variables can be used the same way as other expressions.

Variables Related to Entities in System Module

There are a couple of things to note about using system variables in OQL:

• [%CurrentObject%] is not supported in OQL.
• The [%CurrentUser%] system variable contains an association with the System.User object.
• The [%UserRole_<role name>%] variable contains an association with the object of entity System.UserRole that corresponds to role <role name>.

Both [%CurrentUser%] and [%UserRole_<role name>%] can be used only as references. They cannot be cast to other data types.

For example, this query gets the Name from all Sales.Person objects that are owned by current user:

SELECT
	Name
FROM
	Sales.Person
WHERE
	System.owner = '[%CurrentUser%]'

This query returns the Name from all Sales.Person objects that are owned by users with role Manager:

SELECT
	Name
FROM
	Sales.Person
WHERE
	System.owner/System.User/System.UserRoles = '[%UserRole_Manager%]'

Time-Related Variables

All time-related variables and expressions that are supported in XPath are also supported in OQL. See section Time-Related of XPath Keywords and System Variables.

The return type of all time-related variables and expressions is Date and time. They can be used the same way as values of type Date and time.

For example:

SELECT
	BirthDate,
	DATEPART(YEAR, '[%BeginOfCurrentYear%]') AS CurrentYear,
	DATEDIFF(YEAR, BirthDate, '[%CurrentDateTime%]') AS Age,
	'[%BeginOfCurrentDay%] - 3 * [%YearLength%]' AS TodayThreeYearsAgo
FROM
	Sales.Person

Operators

Operators perform common operations and, unlike functions, do not put their parameters in parentheses. They take expression as input, which can be other operator results, functions, columns and literals.

Supported operators are split into binary, unary, and other operators based on their syntax. These are further subdivided into logical and arithmetic operators, depending on their return type. Logical operators always return a BOOLEAN type. The return type of arithmetic operators depends on the data types of the expressions being operated on. CASE is detailed separately.

Binary Operators

These are the supported binary operators:

Binary operators are used with this syntax:

	expression operator expression

Where operator is any available binary operator. Both expression operands should be of compatible types for the operator and compatible with the other operand.

Type Coercion Precedence

Binary operations perform type casting when operands have different types. For operations involving only numeric types, data types are always upcasted to ensure data types match. The resulting type will be the operand type with the highest precedence according to this ordering:

• DECIMAL
• LONG
• INTEGER

+ (Addition)

Performs different operations depending on the first expression datatype. A numeric input performs a arithmetic addition, while a STRING input performs string concatenation.

Assume Sales.Customer contains two objects and Sales.Order contains three objects.

SELECT * FROM Sales.Customer

SELECT * FROM Sales.Order

The operator can be used to modify an attribute in SELECT.

SELECT LastName, (Number + 5) AS N FROM Sales.Order

It can also be used for complex WHERE comparisons. The following query checks for equality of the full name of a customer:

SELECT LastName FROM Sales.Customer WHERE (FirstName + LastName) = 'JaneMoose'

- (Subtraction)

Subtracts the right expression from the left one. Both operands must be numeric.

Assume Sales.Finances contains two objects:

SELECT * FROM Sales.Finances

We can calculate a profit based on this data:

 Select (Revenue - Cost) as Profit FROM Sales.Finances

* (Multiplication)

Multiplies expressions.

For example, it can be used to get the total value of an order:

SELECT LastName, (Number * Price) as Total FROM Sales.Order

: (Division)

Divides left expression by the right expression. Supports long, integer, and decimal division. In case of long and integer division, the remainder is discarded.

% (Modulo)

Returns the remainder of a division. The behavior is database dependent when one of the expression is of type DECIMAL.

= (Equal To)

Returns TRUE if both expression inputs are equal. When used with NULL, it will always return a FALSE result. To compare to NULL values, use the IS operator.

The = operator is useful for checking exact matches in data. For example, this query retrieves a specific customer’s orders:

SELECT LastName, Number FROM Sales.Order WHERE LastName = Moose

!= (Not Equal To)

Inverse of =. The same NULL handling rules apply. Partial expression expression != is equivalent to NOT expression =.

< (Less Than)

Returns TRUE if the left expression is less than the right. Both expression must be numeric.

It can be used for filtering data with the use of a WHERE clause. For example:

SELECT LastName, Number, Price FROM Sales.Order WHERE Price < 5

<= (Less Than Or Equal To)

Returns TRUE if the left expression is less than or equal to the right. Both expression must be numeric.

> (Greater Than)

Returns TRUE if the left expression is greater than the right. Both expression must be numeric.

>= (Greater Than Or Equal To)

Returns TRUE is the left expression is greater than or equal to the right. Both expression must be numeric.

OR

Returns TRUE if at least one input expression returns TRUE. Both expression must be of type BOOLEAN.

AND

Returns TRUE if both input expression return TRUE. Both expression must be of type BOOLEAN.

Its main use is to make complex WHERE conditions with a combination of input values.

For example, in the following query, large orders or smaller orders with a high value are selected:

SELECT LastName, Number, Price FROM Sales.Order WHERE Number >= 5 OR Price > 4 AND Number >= 3

Note that in the query above AND is evaluated first. The following query with parentheses returns orders that have low volume or low price with a minimum of 3 orders:

SELECT LastName, Number, Price FROM Sales.Order WHERE (Number <= 5 OR Price < 6) AND Number >= 3

Unary Operators

Unary operators only have a single argument. The following unary operators are supported:

Unary operators are used with the following syntax:

	operator expression

expression should be of a type compatible with the operator.

- (Arithmetic Negation)

Negates a numeric value. The return type is the same as the input expression.

NOT

Reverses Boolean TRUE values into FALSE and vice versa.

Other Operators

The operators in this section do not match the general unary or binary syntax. They are all logical operators:

LIKE

Matches an expression to the pattern after the operator.

Syntax

The syntax of the LIKE operator is as follows:

expression LIKE pattern

Where expression is of type STRING and pattern is a string literal or parameter. Note that this means functions are not allowed to be used in pattern. A NULL pattern is treated as an empty string.

The pattern can have special characters, which are all wildcards. The following wildcard characters are supported:

In order to search for special characters, they should be escaped with the \ escape character (including \ itself).

Examples

For example, say we have 3 strings for column PropertyType: Apartment, Tenement, and Flat. We can select all strings ending with “ment” with this condition:

Select PropertyType FROM RealEstate.Properties WHERE PropertyType LIKE '%ment' 

A certain length of string can be enforced with the use of the _ operator This query matches any string that has 4 of any character ending with “ment”:

Select PropertyType FROM RealEstate.Properties WHERE PropertyType LIKE '____ment' 

This query will match any string containing the letter “a” and ending in “t”:

Select PropertyType FROM RealEstate.Properties WHERE PropertyType LIKE '%a%t' 

IN

Matches a value in a subquery or a list of expression values. Each value in the list or subquery is compared to a specified expression with the operator =(Equal to), returning TRUE if any of the comparisons return TRUE. NULL value handling is the same as the =(Equal to) operator.

Syntax

The syntax of the IN operator is as follows:

expression IN {
    subquery
    | ( expression [ ,...n] )
    | parameter
}

Where expression can have any type. The left side can be either a subquery, a comma separated list of expression, or a parameter that is a list of values. If subquery is used, it must return a single column.

Examples

The IN operator is used to create conditions that depend on other entities or limited views of entities.

For example, the condition below checks if the string House is in the literal list on the right, and returns FALSE:

'House' IN ('Apartment','Shed','Shack')

This query retrieves all customers that have an order larger than 3:

SELECT LastName, FirstName
FROM Sales.Customer 
WHERE LastName IN
    (SELECT subq.LastName 
    FROM Sales.Order subq
    WHERE subq.Number > 3)

EXISTS

Returns TRUE if a subquery returns at least one row.

Syntax

The syntax of the EXISTS operator is as follows:

EXISTS subquery

Where subquery is any query.

Examples

The EXISTS operator can be used to check if an entity contains any object matching a condition.

For example, the following condition:

EXISTS (SELECT * FROM Sales.Customer WHERE LastName = 'Mose')

returns FALSE as there are no customers with the last name Mose.

This query returns all customers that also have orders placed:

SELECT *
FROM Sales.Customer customer
WHERE EXISTS
    (SELECT *
    FROM Sales.Order order
    WHERE order.LastName = customer.LastName)

IS

Tests for an expression being NULL. Can be inverted with an optional NOT.

Syntax

The syntax of the IS operator is as follows:

expression IS [ NOT ] NULL

Where expression is an expression of any datatype.

Examples

The IS operator can be used to filter out rows with values that are NULL. For example:

	SELECT Revenue, Cost FROM Sales.Finance WHERE Revenue IS NOT NULL 

CASE

The CASE expression is a conditional expression, similar to if/else statements in other programming languages. If the result of a following WHEN condition is TRUE, the value of the CASE expression is the result that follows the condition and the remainder of the CASE expression is not processed. If the result is not TRUE, any subsequent WHEN clauses are examined in the same manner. If no WHEN condition yields TRUE, the value of the CASE expression is the result of the ELSE clause. If the ELSE clause is omitted and no condition is TRUE, the result is null.

If OQL v2 is enabled, additional data type validations apply to result expressions of CASE. See the corresponding page for details.

Syntax

The CASE expression can be used in two ways – simple:

	CASE input_expression
	{ WHEN when_expression THEN result_expression } [ ...n ]
	ELSE else_result_expression
	END

In a simple CASE expression, input_expression will be compared to when_expression. If input_expression matches when_expression, the result of the whole CASE expression will be result_expression given after THEN. The data types of input_expression and when_expression must tch.

There is also an extended version:

	CASE
	{ WHEN boolean_expression THEN result_expression } [ ...n ] 
	ELSE else_result_expression
	END

In an extended Case expression, boolean_expression is evaluated and if it is TRUE, the result of the whole CASE expression will be result_expression given after THEN. boolean_expression must have return type BOOLEAN.

In both instances, else_result_expression is the result of the whole CASE expression, when no previous when_expression matched or no previous boolean_expression returned TRUE.

Examples

Simple expression:

SELECT
	LastName,
	Number,
	CASE Number
		WHEN 7 THEN True
		ELSE False
		END AS IsLuckyNumber
FROM Sales.Order

Extended expression:

SELECT
	LastName,
	Number,
	Price,
	CASE
		WHEN Price > 7 THEN 'Priority'
		WHEN Number = 7 THEN 'Lucky'
		ELSE 'Regular'
		END AS OrderType
FROM Sales.Order

If result expressions have different numeric types, date type of the result expression in the first WHEN has priority, and the whole CASE expression has type of that result expression. This behavior matches the behavior of supported database vendors.

SELECT
	LastName,
	Number,
	Price,
	CASE Name
		WHEN 'Doe' THEN Price
		ELSE Number
		END AS PriceOrNumber,
	CASE Name
		WHEN 'Doe' THEN Number
		ELSE Price
		END AS NumberOrPrice
FROM Sales.Order

Operator Precedence

If operators are used without parenthesis to indicate order, the order of application is left to right with operator precedence:

• * (Multiplication), : (Division), % (Modulo)
• - (Arithmetic negation), + (Addition), - (Subtraction)
• =, >, <, >=, <=, !=, IS, IN, EXISTS, LIKE
• NOT
• AND
• OR

NULL Handling

If one of the expression in a binary operation or the unary expression have a NULL value, then the return type will also be NULL.

This does not apply to the = and != operators. Handling of NULL in other operators is detailed in the specific operator subsections.

String Coercion

In some databases, using STRING type variables in place of numeric, DATETIME or BOOLEAN values in operators and functions that explicitly require those types, causes the database to perform an implicit conversion. A common example would be the use of a STRING representation of a DATETIME variable inside a DATEPART function. Mendix recommends that you always cast strings to the exact type the operator or functions.

Functions

These are the currently supported functions:

• CAST
• COALESCE
• DATEDIFF
• DATEPART
• LENGTH
• LOWER
• RANGEBEGIN
• RANGEEND
• REPLACE
• ROUND
• UPPER

CAST

The CAST function converts an expression to a specified data type.

Syntax

The syntax is as follows:

CAST ( expression AS data_type )

expression

expression specifies the expression to convert.

data_type

data_type specifies the data type to convert the expression to. The data type can be one of the following:

• BOOLEAN
• DATETIME
• DECIMAL
• INTEGER
• LONG
• STRING

Supported Conversions

The table below describes which CAST conversions are supported:

• ✔ – the conversion is supported
• ✔* – the conversion is supported, but the behavior differs per database
• ✘ – the conversion is not supported

¹BOOLEAN to STRING (limited) is supported only if the resulting string length is greater than or equal to 5.

²The conversion of DATETIME and DECIMAL to STRING (limited) is supported only if the value fully fits into the string length. The conversion can fail if the resulting string length is less than 20.

Converting DATETIME or BOOLEAN to STRING returns different format per database.

Examples

A frequent use case for CAST is to convert your date from the DATETIME data type to a text formatted STRING type:

CAST ( datetime_column AS STRING )

Explicit conversions can also be useful for numeric data types, like ensuring a division operation is a floating point division and the remainder is not discarded:

SELECT (Number : 2) as Normal, (Cast(Number AS DECIMAL) : 2) as Casted FROM Sales.Order Where Number = 7

COALESCE

Returns the value of the first expression that is not NULL. Can be used with columns.

If OQL v2 is enabled, additional data type validations apply to arguments of COALESCE. See the corresponding page for details.

Syntax

The syntax is as follows:

COALESCE ( expression [ ,...n ] )

expression specifies the expression to check. Most databases expect the function to be given at least two expression arguments.

Examples

Assume entity Sales.Customer entity now has some NULL values:

SELECT * FROM Sales.Customer

Selecting a non-null name for a customer, ignoring if it is the first name or last name, can be done with COALESCE:

SELECT COALESCE(LastName, FirstName) AS Name FROM Sales.Customer

If arguments of COALESCE have different numeric types, the expression gets the type of the first argument. This behavior matches the behavior of supported database vendors.

SELECT
	COALESCE(Age, TotalOrderAmount) AS AgeOrAmount,
	COALESCE(TotalOrderAmount, Age) AS AmountOrAge,
FROM Sales.Customer

DATEDIFF

The DATEDIFF function returns the difference between two given DATETIME expressions. The difference is given in the specified unit.

If OQL v2 is enabled, additional data type validations apply to the arguments of DATEDIFF. See the corresponding page for details.

Syntax

The syntax is as follows:

DATEDIFF ( unit , startdate_expression, enddate_expression [, timezone ] )

unit

unit specifies the unit of the DATETIME value to retrieve. This can be one of the following:

• YEAR,
• QUARTER,
• MONTH,
• DAY,
• WEEK,
• HOUR,
• MINUTE,
• SECOND
• MILLISECOND.

For more information on DATETIME values, see the example section under DATEPART, below.

startdate_expression

startdate_expression specifies the start date of the period being calculated. The expression should resolve to a DATETIME value. String representations of DATETIME are accepted.

enddate_expression

enddate_expression specifies the end date of the period being calculated. The expression should resolve to a DATETIME value. String representations of DATETIME are accepted.

timezone

timezone specifies the time zone to use for the retrieval. This parameter is optional and defaults to the local time zone. It should be a string literal containing an IANA time zone. GMT offset time zones are not supported.

Examples

Assume the entity Sales.Period has 2 objects:

SELECT * FROM Sales.Period

You can use DATEDIFF to get the time interval between two dates:

SELECT DATEDIFF(MONTH , End, Start ) as difference FROM Sales.Period

This interval can be used to calculate the average revenue per month:

SELECT Revenue : DATEDIFF(MONTH, End, Start ) as avg_revenue FROM Sales.Period

DATEPART

The DATEPART function retrieves a specified element from DATETIME values. The return type is INTEGER.

If OQL v2 is enabled, additional data type validations apply to the arguments of DATEPART. See the corresponding page for details.

Syntax

The syntax is as follows:

DATEPART ( datepart , date_expression [, timezone ] )

datepart

datepart specifies the part of the DATETIME value to retrieve. For possible values, see the Example below.

date_expression

date_expression specifies the date to retrieve an element from. The expression should resolve to a DATETIME value, string representations of DATETIME are accepted.

timezone

timezone specifies the time zone to use for the retrieval. This parameter is optional and defaults to the local time zone. It should be a string literal containing an IANA time zone. GMT offset time zones are not supported.

Examples

DATEPART can be used to filter dates on specific components. The following query returns all end dates that are in the year “2025”.

SELECT End FROM Sales.Period WHERE DATEPART(YEAR, End) = 2025

LENGTH

Description

The LENGTH function returns the length in characters of the result of a string expression.

If OQL v2 is enabled, additional data type validations apply. See the corresponding page for details.

Syntax

The syntax is as follows:

LENGTH ( expression )

Where expression is an expression of type STRING.

Example

The function is used to get the length of strings and can be used for miscellaneous purposes like statistics. Assume we have an entity Sales.Reports that contains a field with long text:

SELECT * FROM Sales.Reports

You can return an extra column containing the calculated length of the Text as follows:

SELECT Text, LENGTH(Text) as text_length FROM Sales.Reports

LOWER

Description

Converts all uppercase characters in a given string to lowercase.

Syntax

The syntax is as follows:

LOWER ( expression )

expression specifies the string to convert.

Example

The function is useful to enforce consistent case for all strings, especially for comparisons.

For example, the following query would return no results in case-sensitive databases, as there is only a “Doe”:

SELECT * FROM Sales.Customer WHERE LastName = 'doe'

Using LOWER this inconsistency can be fully avoided:

SELECT * FROM Sales.Customer WHERE LOWER(LastName) = 'doe'

Ranges in Datasets

RANGEBEGIN and RANGEEND can only be used with a parameter as input.

RANGEBEGIN

Extracts the initial value of a range parameter.

Syntax

RANGEBEGIN ( $range )

$range specifies the range parameter.

Example

Assume $now is “2024-06-15 00:00:00” and there are 3 range parameters defined in a dataset:

• $range with start value “2024-06-01 00:00:00” and end value “2025-06-01 00:00:00”
• $range_future with start value $now
• $range_past with end value $now

This query uses $range_future to retrieve all periods that end in the future:

SELECT End, Revenue FROM Sales.Period
WHERE End > RANGEBEGIN($range_future)

RANGEEND

Extracts the end value of a range parameter.

Syntax

RANGEEND ( $range )

$range specifies the range parameter.

Example

Using the same data as in the RANGEBEGIN example, this query uses $range to retrieve all periods that end before the end value of $range:

SELECT End, Revenue FROM Sales.Period
WHERE End < RANGEEND($range)

This query uses $range_past to retrieve all periods that ended before the end date of $range_past:

SELECT End, Revenue FROM Sales.Period
WHERE End < RANGEEND($range_past)

REPLACE

The REPLACE function takes an input string and replaces all occurrences of a specified string within it with another string. The function supports limited and unlimited STRING types. Arguments of other types are not supported.

Syntax

The syntax is as follows:

REPLACE ( expression, pattern, replacement )

expression specifies the string to be searched.

pattern specifies the substring to search for. In the function output, all occurrences of the substring will be replaced with the value of replacement.

replacement specifies the string to replace the pattern.

Database-specific limitations

The behavior of the REPLACE function relies on underlying database implementation, which varies by database vendor. For most supported databases, the default behavior of REPLACE is case-sensitive. That means that REPLACE('ABC abc', 'abc', 'xyz') results in 'ABC xyz'. In some configurations, the behavior is case-insensitive. For example, for SQL Server, case sensitivity of REPLACE depends on which collation is used.

Examples

The function is useful if you want to format strings in a consistent manner.

For example, a space delimited list can be converted to one with commas to be used for csv. Assume we have an entity Sales.Raw that contains a STRING field:

SELECT * FROM Sales.Raw

The text can be converted with REPLACE as follows:

SELECT REPLACE(Import, ' ', ',') FROM Sales.Raw

ROUND

Rounds a numeric expression by reducing precision after the decimal point.

Syntax

The syntax is as follows:

ROUND ( expression , length )

expression

expression is any numeric expression to be rounded. If expression is NULL, the function will return NULL.

length

length specifies the number of decimal places to which the expression must be rounded. It must be of a numeric type. If the length is NULL, the function result will be NULL.

Examples

The function can be used to check the equality of decimal values. In this query a small difference between decimal columns means that no results are returned:

SELECT LastName, Number FROM Sales.Order WHERE Price = 1.50000001

You can modify it with the use of ROUND to only compare to two decimal places:

SELECT LastName, Price FROM Sales.Order WHERE ROUND(Price, 2) = ROUND(1.50000001, 2)

Operations like division with DECIMAL data type can produce a large number of digits after the decimal point. ROUND can be used to reduce the precision when these are not needed:

SELECT ROUND((Price : 7), 2) as RoundedPrice, Price : 7 FROM Sales.Order

UPPER

Converts all lowercase characters in a given string to uppercase. Opposite of LOWER.

Syntax

The syntax is as follows:

UPPER ( expression )

expression specifies the string to convert.