Issue: Running Count analytic function working in Oracle but failing in Snowflake due to GROUP BY enforcement
We have a Running Count object defined in the universe as a Dimension, where the SQL uses an analytic function (COUNT … OVER (PARTITION BY … ORDER BY …)).
-
When the report runs against Oracle, the query works fine even though this object is not included in the GROUP BY clause.
-
After migrating the universe to Snowflake, the same report fails with the error:
“SQL compilation error: not a GROUP BY expression / object is not part of GROUP BY”
In the universe, this object was intentionally designed not to participate in GROUP BY, as it is an analytic calculation and Oracle allows this behavior.
However, Snowflake enforces stricter SQL standards and requires all non-aggregated selected expressions to be either:
- Included in the
GROUP BY, or - Fully derived from analytic functions without conflicting aggregation at the same query level.
Because of this difference, the query generated by BO fails in Snowflake even though it worked in Oracle.
Any suggestions from teams who faced similar Oracle → Snowflake BO migration issues would be helpful.
COUNT (
datedim.“hiredate”)
OVER (
PARTITION BY View__3.“empid”
ORDER BY
( datedim.“HIRE_DATE” DESC,
datedim.“NAME” ASC,
how do we fix in IDT