Infor M3 #9 -- Aggregate functions in DB instances

Aggregate functions in database tables

Sometimes we have to fetch aggregated values of a column in our logic to perform some other operation based on the value. Generally, to fetch aggregated values we loop through all the records and calculate the aggregated value.

Isn't it tedious to loop through all the records and calculate the value? and so the usage of CPU would be more and the performance would be a bit low.

To avoid such kind of scenarios we have standard aggregate functions in Infor M3's tables. Using these methods we can fetch the aggregated value just like we fetch in SQL.

Below are the aggregated functions available in Infor M3

• AVG
• COUNT
• SUM
• MIN
• MAX

Let's see the syntax and the implementation of each method 

AVG:

• This function calculates the Average of the selected column just like SQL
• You have to set the keys to be used before executing this method i.e., where clause of a table
• getAvg() is the method name, which is a class member of AggregateResult class, the return type of this method is double
• You get the instance of AggregateResult using the table instance as mentioned below
• Let's see the syntax and how this method is used
• Set the primary keys of the table on which the aggregation has to happen
• Get AggregateResult class's instance using .aggregate() method
• using getAvg(), get average of a column by passing the index and the column name in the parameters

To get AggregateResult class's instance you can use the below method in two ways as mentioned below.

1. aggregate(String LF, String ColumnName);
2. aggregate(String LF, String ColumnName, MvxRecord key);

LF refers to the logical index on which the aggregation has to be done.
ColumnName refers to the column on which the aggregation has to be done.
key refers to the where clause of the table in case you would like to use MvxRecord, this would be used when you use MvxRecord to set the keys of the table. If you use the normal mechanism where you set values using the table instance then you have to use 1 method.

Let's take an example where you want to retrieve the Average of a customer order's quantity

//Set the primary keys of OOLINE

OLINE.setCONO(OHEAD.getCONO());

OLINE.setORNO().moveLeftPad(OHEAD.getORNO());

//Get the instance of AggrageResult

AggregateResult  aggregateResult  = OLINE.aggregate("00", "OBORQA");

//Get the average of the ORQA using getAvg()

double averageORQA = aggregateResult.getAvg(); 

COUNT:

• This function returns the number of rows available in the table which are matching the keys you have passed
• You have to set the keys to be used before executing this method i.e., where clause of a table
• getCount() is the method name which is a class member of AggregateResult class, the return type of this method is long
• As discussed above you can get AggrgateResult's instance using any one of the methods

Let's take an example where you want to retrieve the Number of customer order lines available

//Set the primary keys of OOLINE

OLINE.setCONO(OHEAD.getCONO());

OLINE.setORNO().moveLeftPad(OHEAD.getORNO());

//Get the instance of AggrageResult

AggregateResult  aggregateResult = OLINE.aggregate("00", "OBORNO");

//Get the count of the records matching the keys using getCount()

long numberOfRows = aggregateResult.getCount(); 

SUM:

• This function calculates the sum of the selected column values which are matching the keys
• You have to set the keys to be used before executing this method i.e., where clause of a table
• getSum() is the method name, which is a class member of AggregateResult class, the return type of this method is double
• As discussed above you can get AggrgateResult's instance using any one of the methods

Let's take an example where you want to retrieve the Sum of customer order's quantity

//Set the primary keys of OOLINE

OLINE.setCONO(OHEAD.getCONO());

OLINE.setORNO().moveLeftPad(OHEAD.getORNO());

//Get the instance of AggrageResult

AggregateResult  aggregateResult = OLINE.aggregate("00", "OBORQ");

//Get the sum of the ORQA using getSum()

double totalORQA = aggregateResult.getSum();

MIN:

• This function returns the minimum value of the selected column in the list of records which are matching the keys
• You have to set the keys to be used before executing this method i.e., where clause of a table
• getMinxxxxx() is the method name, which is a class member of AggregateResult class, the return type of this method is double/long/MvxString depends on the type of column as mentioned below
• As discussed above you can get AggrgateResult's instance using any one of the methods
• getMINxxxx() can be used for three types of the column as mentioned below
• if the column type is MvxString then you have to use getMinMvxString()
• if the column type is long then you have to use getMinLong()
• if the column type is double then you have to use getMinDouble()

Let's take an example where you want to get the Minimum ordered value of a customer order

//Set the primary keys of OOLINE

OLINE.setCONO(OHEAD.getCONO());

OLINE.setORNO().moveLeftPad(OHEAD.getORNO());

//Get the instance of AggrageResult

AggregateResult  aggregateResult = OLINE.aggregate("00", "OBORQ");

//Get the minimum ordered value using getMinDouble()

double minORQA = aggregateResult.getMinDouble();

MAX:

• This function returns the maximum value of the selected column in the list of records which are matching the keys
• You have to set the keys to be used before executing this method i.e., where clause of a table
• getMaxxxxxx() is the method name, which is a class member of AggregateResult class, the return type of this method is double/long/MvxString depends on the type of column as mentioned below
• As discussed above we get AggrgateResult's instance using any one of the methods
• getMINxxxx() can be used for three types of the column as mentioned below
• if the column type is MvxString then you have to use getMaxMvxString()
• if the column type is long then you have to use getMaxLong()
• if the column type is double then you have to use getMaxDouble()

Let's take an example where you want to get the Maximum ordered value of a customer order

//Set the primary keys of OOLINE

OLINE.setCONO(OHEAD.getCONO());

OLINE.setORNO().moveLeftPad(OHEAD.getORNO());

//Get the instance of AggrageResult

AggregateResult  aggregateResult = OLINE.aggregate("00", "OBORQ");

//Get the maximum ordered value using getMaxDouble()

double maxORQA = aggregateResult.getMaxDouble();

That's it, come back here and tell us about the before-and-after. I bet you'll have something to say!

Please let me know your thoughts in the comment section below!

If you enjoyed this post, I'd be very grateful if you'd help it spread by emailing it to a friend, or sharing it on Twitter or LinkedIn.

In our next post let us see about How to use field selections to avoid table modifications, how they can be used in filtering the data in wildcard entry search and the selection criteria on any field of the table

Thanks for reading!

--Saiteja Saadi