Using CurrDate() in aggregate join gives error

Using CurrDate() in aggregate join gives error

  
I have 2 tables for an errorhandling system (for another application), one entity defines ErrorDefinition
and another keeps Statistics how often each ErrorDefinition was matched on a daily basis.

In an aggregate i want to sum the number of times the ErrorDefinition was matched in the past 7 days.

My first instinct was to add this as a join (With or Without):
ErrorDefinition.Id = Statistics.ErrorDefinitionId
and DiffDays(Statistics.ErrorDate, CurrDate()) <= 7

However that gives the following error:
Must declare the scalar variable "@qepCurrdate".

So i can work around this error by adding the following to a filter
DiffDays(Statistics.ErrorDate, CurrDate()) <= 7
or Statistics.ErrorDefinitionId = NullIdentifier()

Then the aggregate returns what i wanted it to, however i have a major concern:
Will Outsystems now first select all values in Statistics that relate to the ErrorDefinition, pull them over the line from the database and then afterwards in the Outsystems server reduce the list to only include the last 7 days?
In that case it would use much unneeded resources.
Can anyone shed a light on this?


As i reflected on this issue later, probably the Outsystems engine does some optimizations that will do the 'filtering' directly in the query sent to the database as the same is true for adding a filter by ID.

Are filters always done directly on the query before they are sent to the database? (so pre-dataretrieval)
Or can they also (as their name suggests) do their work post-dataretrieval.

The second option would allow for large datasets being retrieved and the Outsystems server having to filter out most of it again causing unneeded load on the database, the network and the ousystems server so i really hope that filters per-definition do their magic pre-dataretrieval.
One trick I use when trying to understand what's going on with an Aggregate, is inspect the SQL that is being generated.



Hope this helps :)
João Fernandes wrote:
One trick I use when trying to understand what's going on with an Aggregate, is inspect the SQL that is being generated.



Hope this helps :)
 
 Yes that helped, i can see it put's all my Filter conditions in the WHERE clause, so all the 'filtering' is done pre-dataretrieval which is a good thing performance wise!

Thank you very much for pointing that out.