The following provides an overview for the rules and restrictions that govern OLAP functionality.
SAP Sybase IQ provides SQL OLAP functions with rules, restrictions and limitations.
In the SELECT list
In expressions
As arguments of scalar functions
In the final ORDER BY clause (by using aliases or positional references to OLAP functions elsewhere in the query)
OLAP functions cannot be used under these conditions:
In subqueries.
In the search condition of a WHERE clause.
As arguments for SET (aggregate) functions. For example, the following expression is invalid:
SUM(RANK() OVER(ORDER BY dollars))
A windowed aggregate cannot be an argument to argument to another unless the inner one was generated within a view or derived table. The same applies to ranking functions.
Window aggregate and RANK functions are not allowed in a HAVING clause.
Window aggregate functions should not specify DISTINCT.
Window function cannot be nested inside of other window functions.
Inverse distribution functions are not supported with the OVER clause.
Outer references are not allowed in a window definition clause.
Correlation references are allowed within OLAP functions, but correlated column aliases are not allowed.
Columns referenced by an OLAP function must be grouping columns or aggregate functions from the same query block in which the OLAP function and the GROUP BY clause appear. OLAP processing occurs after the grouping and aggregation operations and before the final ORDER BY clause is applied; therefore, it must be possible to derive the OLAP expressions from those intermediate results. If there is no GROUP BY clause in a query block, OLAP functions can reference other columns in the select list.
The SAP Sybase IQ limitations with SQL OLAP functions are:
User-defined functions in a window frame definition are not supported.
The constants used in a window frame definition must be unsigned numeric value and should not exceed the value of maximum BIG INT 263-1.
Window aggregate functions and RANK functions cannot be used in DELETE and UPDATE statements.
Window aggregate and RANK functions are not allowed in subqueries.
CUME_DIST is currently not supported.
Grouping sets are currently not supported.
Correlation and linear regression functions are currently not supported.