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