S02046
Error Code
S02046
Error Message
Analytic functions are not allowed in WHERE clause, GROUP BY clause, or to be
nested in aggregate functions. RefId: S02046
Probable Causes
This error occurs when an analytic function is applied in a
where
clause, group by
clause, or nested in an aggregate function or
user-defined function. For
example:id = `XOM`GS`AAPL
val = 102.1 33.4 73.6
date = 2020.01.01 2020.01.02 2020.01.01
t = table(id, val, date);
select id from t where max(val) over (partition by date rows 1 preceding) > 50
select id from t group by max(val) over (partition by date rows 1 preceding)
select min(max(val) over (partition by date rows 1 preceding)) from t
=> Analytic functions are not allowed in WHERE clause, GROUP BY clause, or to be nested in aggregate functions.
Solutions
Restructure the query to separate the analytic function from the
where
clause, group by
clause, aggregate
function, or user-defined function.select
id
from
t
where
(
exec
max(val) over (partition by date rows 1 preceding)
from
t
) > 50