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
aggregated calculation in a query
Re: aggregated calculation in a query
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
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
Re: aggregated calculation in a query
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
I think the best way will be to create a view in the database and create a BO of type existing external...
br
Evelyn
Re: aggregated calculation in a query
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
Bruce
-
- Posts: 1460
- Joined: Tue Jan 24, 2017 5:51 am
- Location: 'Stralya
Re: aggregated calculation in a query
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.
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.