S02048

Error Code

S02048

Error Message

Order-sensitive functions are not allowed in ORDER BY clause within an OVER clause. RefId: S02048

Probable Causes

Using order-sensitive functions, such as moving functions, prev, and next, in the order by clause within an analytic function can lead to inconsistent results for the same script. 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 sum(val) over (order by msum(val, 2)) from t => Order-sensitive functions are not allowed in ORDER BY clause within an OVER clause.v

Solutions

  • Modify the SQL statement.
  • Use a user-defined function (not recommended):
    def summ(val, window) {
      return msum(val, window)
    }
    
    select sum(val) over (order by summ(val, 2)) from t