SYMPTOM:
A report that executed successfully in Strategy 9.4.1 fails after an upgrade to Strategy 10.4. The report includs metrics defined using ApplySimple function.
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
CAUSE:The error occurs when the Strategy SQL engine incorrectly places a subquery or an aggregation function inside another aggregation function for SQL Global Optimization level 4. Microsoft SQL server does not support aggregate functions that contain a subquery or another aggregate function. The Strategy SQL engine detects and avoids aggregate functions or subqueries for the SQL optimization level 4. However when the aggregate function or the subquery is placed into an ApplySimple condition, the Strategy SQL engine does not detect the invalid SQL statement.
ACTION:Strategy will continue to investigate and post updates as soon as they are available.
WORKAROUND:Modify the VLDB setting of the report, under query optimizations select SQL Global Optimization. Change the default value to SQL Global Optimization Level 2.
It is recommended to change the value at the report level, and keep the SQL Global optimization level 4 at the project level as the SQL Global Optimization level 4 has shown a significant improvement in report execution time.
