aggregated calculation in a query

If you have questions or if you want to share your opinion about Aware IM post your message on this forum
Post Reply
ekwo
Posts: 15
Joined: Mon Nov 29, 2021 11:16 pm

aggregated calculation in a query

Post by ekwo »

Hi
I find some old posts saying you can't use WHERE in a calculation.

I want to add a calculated column to my query, telling me if there exists details for this record.

I have a query fetching all Departments
and I want a column that returns count(employees) belonging to the department

How can I do this? or even just an indicator (yes or no) if there are og aren't any employees registered for the departments.

br
Evelyn
BLOMASKY
Posts: 1470
Joined: Wed Sep 30, 2015 10:08 pm
Location: Ocala FL

Re: aggregated calculation in a query

Post by BLOMASKY »

You can have a rule

IF BO WAS CHANGED THEN
BO.calcColumn = COUNT ChildBO WHERE ChildBO.linkedField = BO


The problem with this, is that it is ONLY triggered when the Parent BO was changed, so you would also need a rule in the Child that does the reverse
IF ChildBO WAS CHANGED THEN
ChildBO.obParentBO.calcColum = .....


Bruce
ekwo
Posts: 15
Joined: Mon Nov 29, 2021 11:16 pm

Re: aggregated calculation in a query

Post by ekwo »

Thank you Bruce.
I think the best way will be to create a view in the database and create a BO of type existing external...

br
Evelyn
BLOMASKY
Posts: 1470
Joined: Wed Sep 30, 2015 10:08 pm
Location: Ocala FL

Re: aggregated calculation in a query

Post by BLOMASKY »

I use both views and stored procedures to populate Aware BOs often in my applications. I let Aware do what it does best and let SQL do what it does best. Its easy - peasy to have a SP to delete the data in a BO, then insert the appropriate records. (and, it is fast!).

Bruce
PointsWell
Posts: 1457
Joined: Tue Jan 24, 2017 5:51 am
Location: 'Stralya

Re: aggregated calculation in a query

Post by PointsWell »

The alternative is to place a field on Department to hold the information and add a rule to say if Child inserted in Parent.omChildren then count child where child.obParent = Parent

The cost of this is a number column plus the processing effort.

The cost of a calculated column is the calculation * number of records

The cost of an external view / stored procedure is the risk that you change an attribute name which then breaks your view / stored proc or you export the application to another database and you forget to move your view / sp.
Post Reply