OQL Expressions

Introduction

An OQL expression is a query building block that returns a value or a list of values. Expressions can be one of the following:

• a constant
• a function
• a system variable
• a subquery
• a combination of attribute names, constants, system variables, functions, and subqueries connected by operators

OQL expressions can be used in WHERE, SELECT, GROUP BY, UNION, HAVING, and ON conditions of JOIN clauses. For more information, see OQL clauses.

Aggregations

Aggregations are functions that reduce a list of values from a retrieved column (or columns) into a single value. They can be used in the following ways:

• as an attribute in a SELECT clause
• as a condition in a HAVING clause

When combined with a GROUP BY clause, aggregations in SELECT clauses can be used with any column normally available, not just those specified in the GROUP BY clause. Aggregation is performed over groups separately in this case.

Syntax

  COUNT ( * )
  | { COUNT | AVG | MAX | MIN | SUM } ( [ DISTINCT ] attribute_path )
  | STRING_AGG ( attribute_path )

Where attribute_path is an attribute reachable from entities defined in the FROM and JOIN clauses.

COUNT

Calculates the number of rows in a column. Counting multiple columns using COUNT(*) returns a count of all rows.

When counting a single column, rows with a NULL value are not counted.

AVG

Calculates the mean of numerical (INTEGER, DECIMAL, and LONG) values. NULL values are ignored.

MAX and MIN

Returns the maximum or minimum value from a column, with all data types being supported.

• Boolean values are treated as 0 and 1
• Strings are compared alphabetically

NULL values are ignored.

SUM

Calculates the sum of numerical values.

NULL values are ignored.

STRING_AGG

Combines multiple string values from a specified column into a single string. Each value is separated from the next by a specified separator string.

NULL is ignored, empty strings are included.

The syntax of STRING_AGG differs from other aggregations and is as follows:

STRING_AGG ( attribute_path, separator )

separator is any expression of type STRING.

Examples

In the following examples, the Sales.Product entity has five objects with Name and Stock attributes:

SELECT Name, Stock FROM Sales.Product

COUNT

The number of rows can be calculated with COUNT:

SELECT COUNT(*) AS ProductCount FROM Sales.Product

The same result can be retrieved by using COUNT on a single attribute:

SELECT COUNT(Name) AS NameEntryCount FROM Sales.Product

As there is a NULL value in the Stock column, when using COUNT it will be ignored:

SELECT COUNT(Stock) AS StockEntryCount FROM Sales.Product

You can count just unique names with DISTINCT:

SELECT COUNT(DISTINCT Name) AS DistinctNameEntryCount FROM Sales.Product

AVG

The average stock per product entry:

SELECT AVG(Stock) AS StockAverage FROM Sales.Product

There are duplicate values in the Stock column, which can be ignored by using DISTINCT. The query below returns the average unique stock:

SELECT AVG(Distinct Stock) AS DistinctStockAverage FROM Sales.Product

MAX

The most stock of any one product:

SELECT MAX(Stock) as StockMax FROM Sales.Product

To return the name(s) of the product(s) with the highest stock level you have to use a subquery. The subquery returns the maximum stock number, which is then compared to each product’s stock in the WHERE clause:

SELECT HighestStockProductName FROM Sales.Product
WHERE Stock = (SELECT MAX(P.Stock) FROM Sales.Product P)

SUM

The sum of all products in stock:

SELECT Sum(Stock) AS StockSum FROM Sales.Product

The sum of unique product entries:

SELECT Sum(DISTINCT Stock) AS DistinctStockSum FROM Sales.Product

STRING_AGG

You can aggregate product names into a single list:

SELECT STRING_AGG(Name, ',') as ProductNames FROM Sales.Product

Parameters

Parameters are external variables that are referenced to by name in an OQL query. To use a defined parameter in a query, prepend the $ sign to the name of the parameter.

If you use undefined in IN and LIKE comparison expressions, the condition always returns true. In other cases, undefined parameters cause an exception.

Examples

Defined Parameters

In these examples the valid parameters $age and $limit, both of type INTEGER, have been defined.

The following query uses the parameter in the WHERE clause, returning a result set with person names whose age is higher than the value specified in the $age parameter.

SELECT Name
FROM Sales.Person
WHERE
    Age > $age

The following query will return a limited number of rows, limited by the value of the $limit parameter in the LIMIT clause.

SELECT Name
FROM Sales.Person
LIMIT $limit

Undefined Parameters

As an example, take a query with two comparisons using the parameter $param:

SELECT Name
FROM Sales.Person
WHERE
    Age > $param 
    OR
    Job = 'Sales'

If the value of $param is not provided as a parameter to the query, the query will be equivalent to:

SELECT Name
FROM Sales.Person
WHERE
    TRUE
    OR
    Job = 'Sales'

The example above is different from the case where the value of $param is provided, but is NULL. In that case, the query will be equivalent to:

SELECT Name
FROM Sales.Person
WHERE
    Age > NULL
    OR
    Job = 'Sales'

If you use an undefined parameter used in a LIMIT, will throw an exception.

SELECT Name
FROM Sales.Person
ORDER BY LastName
LIMIT $param