Neoview SQL Reference Manual (R2.4)

SELECT num, workgroupnum, annualsalary,
SUM(AnnualSalary) OVER (ORDER BY SUM(annualsalary)*num ROWS
UNBOUNDED PRECEDING),
AVG(AnnualSalary) OVER (ORDER BY SUM(annualsalary)*num ROWS UNBOUNDED
PRECEDING),
MIN(AnnualSalary) OVER (ORDER BY SUM(annualsalary)*num ROWS UNBOUNDED
PRECEDING),
MAX(AnnualSalary) OVER (ORDER BY SUM(annualsalary)*num ROWS UNBOUNDED
PRECEDING),
VARIANCE(AnnualSalary) OVER (ORDER BY SUM(annualsalary)*num ROWS
UNBOUNDED PRECEDING),
STDDEV(AnnualSalary) OVER (ORDER BY SUM(annualsalary)*num ROWS
UNBOUNDED PRECEDING),
COUNT(AnnualSalary) OVER (ORDER BY SUM(annualsalary)*num ROWS
UNBOUNDED PRECEDING),
FROM employee
GROUP BY num, workgroupnum, annualsalary;
Limitations for Window Functions
These limitations apply to all window functions.
• The ANSI window-clause is not supported in Neoview. Only the
inline-window-specification is supported. An attempt to use an ANSI
window-clause will result in a syntax error.
• The window-frame-clause cannot contain a FOLLOWING term, either explicitly or
implicitly. Since the default window frame clause contains an implicit FOLLOWING (“ROWS
BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING”), the default
is not supported. So, practically, the window-frame-clause is not optional. An attempt
to use a FOLLOWING term, either explicitly or implicitly will result in the “4343” error
message.
• The window frame units can only be ROWS. RANGE is not supported in Neoview. An
attempt to use RANGE will result in a syntax error.
• The ANSI window-frame-exclusion-specification is not supported in Neoview.
An attempt to use a window-frame-exclusion-specification will result in a syntax
error.
• Multiple inline-window-specifications in a single SELECT clause are not supported.
For each window function within a SELECT clause, the ORDER BY clause and PARTITION
BY specifications must be identical. The window frame can vary within a SELECT clause.
An attempt to use multiple inline-window-specifications in a single SELECT clause
will result in the "4340" error message.
• The ANSI null-ordering-specification within the ORDER BY clause is not supported
in Neoview. Null values will always be sorted as if they are greater than all non-null values.
Note that this is slightly different than a null ordering of NULLS LAST. An attempt to use
a null-ordering-specification will result in a syntax error.
• The ANSI filter-clause is not supported for window functions in Neoview. Note that
the filter-clause applies to all aggregate functions (grouped as well as windowed) and
that the filter-clause is not currently supported for grouped aggregate functions. An
attempt to use a filter-clause will result in a syntax error.
• The DISTINCT value for the set-qualifier-clause within a window function is not
supported. Only the ALL value is supported for the set-qualifier-clause within a
window function. An attempt to use DISTINCT in a window function will result in the
“4341” error message.
482 OLAP Functions