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.