aggregated calculation in a query

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

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

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

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

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.