Support for QUALIFY clause

Detail name	Value
Changelog Number	9370
Type	New Feature
Status	Resolved
Fix Versions	Exasol 7.0.3
Resolution Date	2020-10-15

Description

Exasol supports analytic functions, but these cannot be accessed in WHERE or HAVING clauses.

Example

create  table t (a int, b int);
insert into t values (1,2), (1,4), (1,3), (2, 1), (2,5);

The below query will return the following error: Analytic functions may only be used as select list elements

select sum(b) over (partition by b order by b) s from t where local.s > 2;

New Feature

Exasol now implements the QUALIFY clause as an extension of the SQL standard. It is possible to reference the result of analytic functions in the QUALIFY condition:

select sum(b) over (partition by a order by b) s from t qualify s > 2;
select a,b from t qualify sum(b) over (partition by a order by b) > 2;

You can find the exact syntax diagram in our documentation