Data Aggregation using OQL

0
Hello! I’m using OQL to retrieve a Decimal Value from Entity. However, I haven’t found out any solution how I can sum up all the value. GROUP BY, SUM are not working. Please see the query wording below and Domain Model. 'SELECT      P.id OPEX_Total_OPEX,     P.Value OTotal FROM     PM_Tool.OPEX P '  
asked
4 answers
0

**Editted my answer **

No need for OPEX_Total. Try this:

FROM PM_Tool.OPEX P
GROUP BY P.Category as Category
SELECT Category, SUM(P.Value) as Total

Does this work?

Also, for creating and trying out OQL’s, and for lots of examples, see the demo-page https://service.mendixcloud.com/p/OQL

Call it from a microflow, just feed it an OQL-boject, containing a valid OQL in the attribute ‘OQL’:

answered
0

Hi Dmitri,

There are two ways for you to solve this:

  1. Using OQL, which is simplest, elegant, fastest and very powerful. If you are facing any issues with OQL queries, kindly share the screenshots of the same and people here will help you out with the errors
  2. If you don’t want to use OQL, you can due the additional entity, the way you have shown in your domain model, in that case, simple retrieve the entire data into a list and than use a loop to calculate the sum for each category and assign it to another additional entity. 

I would highly recommend #1 

Regards,

Shekhar

answered
0

Dear Shekhar,

I'm trying to use OQL (via "Execute OQL statement" element) if you suggested below. What I need to extract the sum of all "Value" as list (for ListView on the page) from OPEX table filtered by Year and Category.

I’m struggling with getting “Category” (Enumeration) and “Year” (Date and Time) from the persistent Entity “OPEX” to non-persistent Entity “OPEX_Total”. The system gives me an error. “Comment” and “Value” fields are fed without any issue.

The following simple code works:

'

SELECT

              P.id OPEX_Total_OPEX,

              P.Value OTotal,

              P.Comment OComment

FROM

              PM_Tool.OPEX P

'

But when I apply more complex functionality the system crushes. The following code doesn’t work. The syntax might be wrong, but I tried to feed only “Year” or only “Category” with no success:

'

SELECT

              P.id OPEX_Total_OPEX,

              P.Value OTotal,

              P.Year OYear

FROM

              PM_Tool.OPEX P

Where P.Category = P.Category.Vendor_External_Maintenance

'

answered
0

Dear Dmitrii,

There is an issue with your OQL query, kindly use the query as below:

  1. To get total by Category

 

SELECT  category,

               SUM(Value)  Cat_Total

FROM   PM_Tool.OPEX

GROUP By category

 

  1. To get total by Year

 

SELECT  year,

 SUM(Value)  Year_Total

FROM   PM_Tool.OPEX

GROUP By year

 

SELECT  category,

              year,

 SUM(Value)  CatYear_Total

FROM   PM_Tool.OPEX

GROUP By category, year

 

Execute these statements from ExcuteOQLStatement action from a microflow. I am showing screenshots below of one of my OQL statements

 

answered