Execute OQL statement syntax for MAX and MIN expressions

0
The documentation for the OQL module is extremely sparse and poorly written, making it frustrating to learn. The OQL documentation for OQL Aggregation is meaningless and has no examples. Please explain how to use MAX and MIN.   For example, for an entity named Animal in module Zoo with the following attributes, how do you select the row with the highest and lowest NumberOfSpecimens (MAX and MIN)?    Animal ------ CommonName (String) NumberOfSpecimens (Integer) Description (String) IsEndangered (Boolean) ExhibitOpenedDate (Date and time)   I have created a non-persistable entity called NumberOfSpecimensMax for the result with only one attribute, NumberOfSpecimens, that looks like this:   NumberOfSpecimensMax -------------------- NumberOfSpecimens (Integer)   In the Execute OQL Statement microflow activity I set the "Return entity" to Zoo.NumberOfSpecimensMax. I have no idea what to set the "Amount", "Offset", and "Preserve parameters" to because the documentation does not explain this (or much else), so I set them to empty, empty, and true, respectively. I set the "Statement" to this:   'SELECT MAX(NumberOfSpecimens) FROM Zoo.Animal'   I get this error:   Caused by: com.mendix.systemwideinterfaces.MendixRuntimeException: java.lang.NullPointerException: Could not find result attribute null in target object. at com.mendix.util.classloading.Runner$.withContextClassLoader(Runner.scala:23)   Please explain how to use MAX and MIN in an Execute OQL Statement activity in a microflow. There is no documentation and I have spent hours trying different things but have had no success. And tell the developers of the OQL module to write some documentation that will be helpful, and improve the OQL Aggregation documentation with a better explanation and examples.    I am using Mendix Studio Pro 9.24.14 (not 9.24.10, which was the closest in the dropdown).
asked
1 answers
0

After much trial and error, and after discovering some anomalies in the behavior of the OQL module (ie; "bugs"), here's the OQL query to get the maximum NumberOfSpecimens of all rows in the table.

 

'SELECT MAX(NumberOfSpecimens) NumberOfSpecimens
FROM Zoo.Animal'

 

This also works:

 

'SELECT MAX(NumberOfSpecimens) AS NumberOfSpecimens
FROM Zoo.Animal'

 

I had to add the 'NumberOfSpecimens' alias after 'MAX(NumberOfSpecimens)' to populate the NumberOfSpecimens attribute of the NumberOfSpecimensMax (non-persistable) entity that gets created because I specified that the "Return entity" is NumberOfSpecimensMax. You have to make sure that the column name exactly matches the name of the attribute of the entity you populate. Without adding the 'NumberOfSpecimens' alias the column name returned would be something like 'max' which does not match the name of the attribute. If the column name does not match an attribute in the Return entity, you will get the error 'Could not find result attribute null in target object.'

 

Incidentally, it's okay for the OQL result to populate only one of several columns in the return entity. You don't have to populate all the attributes of the return entity. You just have to make sure the name of the column matches the name of the attribute in the entity.

 

Example using a variable in the WHERE clause

 

If I want to pass in a parameter to get the MAX of endangered animals (instead of hard-coding it in the WHERE clause), I have to add the "Add boolean parameter" activity before the "Execute OQL statement" activity. The Input would be:

Name: 'IsEndangered'

Value: true

Variable name: IsEndangeredParam

 

Then the OQL statement would look like this:

 

'SELECT MAX(NumberOfSpecimens) NumberOfSpecimens
FROM Zoo.Animal
WHERE IsEndangered = $IsEndangeredParam'

 

Incidentally, you can use an alias for the entity which is essential if you join multiple entities and have attributes with the same name. So this works too:

 

'SELECT MAX(A.NumberOfSpecimens) NumberOfSpecimens
FROM Zoo.Animal A
WHERE A.IsEndangered = $IsEndangeredParam'

 

Anomaly ("bug")

 

The anomaly is that if you join to another table, for example with INNER JOIN, and use one of the Add Parameter actions, for example "Add string parameter" in the WHERE clause of a column in the joined table to get the MAX and you don't have that column in the SELECT clause (requiring you to use GROUP BY), it will simply ignore whatever parameter you use. If you hard code the parameter in the SELECT instead of using a replacement value, it works just fine. I'll demonstrate.

 

Assuming an additional entity called City that looks like this:

 

City
----
CityName (String)
ZooName (String)
NumberOfExhibits (Integer)
NumberOfStaff (Integer)
Description (String)
OpeningDate (Date and time)

 

It has an Association to Zoo named Animal_City.

 

If you "Add string parameter" with:

Name: 'CityName'

Value: 'City/CityName' (from a microflow parameter with entity City)

Variable name: CityNameParam

 

'SELECT MAX(A.NumberOfSpecimens) NumberOfSpecimens
FROM Zoo.Animal A
INNER JOIN A/Zoo.Animal_City/Zoo.City C
WHERE C.CityName = $CityNameParam'

 

This will always return the MAX NumberOfSpecimens regardless of the $CityNameParam specified. In other words, it ignores the WHERE clause C.CityName = $CityNameParam.

 

If you hard-code the CityName, like the following, it works just fine, and will return the MAX NumberOfSpecimens for that city ('San Diego').

 

'SELECT MAX(A.NumberOfSpecimens) NumberOfSpecimens
FROM Zoo.Animal A
INNER JOIN A/Zoo.Animal_City/Zoo.City C
WHERE C.CityName = ''San Diego'''

 

Of course that defeats the purpose of using a string (or other) parameter to dynamically modify the WHERE clause. To get this to work properly, you have to include the name of the column in the WHERE clause as a SELECT column which then requires that you use a GROUP BY to get it to work.

 

'SELECT C.CityName AS CityName, MAX(A.NumberOfSpecimens) AS NumberOfSpecimens
FROM Zoo.Animal A
INNER JOIN A/Zoo.Animal_City/Zoo.City C
WHERE C.CityName = $CityNameParam
GROUP BY C.CityName'

 

Of course now you are getting the MAX NumberOfSpecimens for each city, returning multiple rows, so you'll have to add an additional action to get the Maximum of all the rows; for example by using an Aggregate List activity on the returned list using Function Maximum on Attribute NumberOfSpecimens to get the absolute MAX of NumberOfSpecimens of all animals at zoos in all the cities.

 

answered