QUALIFY Clause in Oracle – Alternative

Post author:Vithal S
Post last modified:May 19, 2022
Post category:General
Reading time:4 mins read

If you are migrating from Teradata to Oracle or Redshift, you will notice that QUALIFY clause is not available. The feature such as QUALIFY clause is widely used in Teradata. But, the same feature is not available in Oracle. In this article, we will check what is QUALIFY Clause alternative in Oracle.

QUALIFY Clause in Oracle

The relational databases such as Teradata uses QUALIFY clause to filter the result of ordered analytical function. The Oracle database does not support this feature yet. But, when you are migrating Teradata scripts to Oracle, you may have to come up with the alternate approach.

Before going into alternative approach, let us first check how QUALIFY clause works.

QUALIFY Clause

A conditional clause in the SELECT statement that filters results of a previously computed ordered analytical function according to user‑specified search conditions.

The following example uses analytical functions and a QUALIFY clause.

SELECT StoreID, SUM(profit) OVER (PARTITION BY StoreID)
   FROM facts
   QUALIFY SUM(profit) OVER (PARTITION BY StoreID) > 2;

The QUALIFY is a conditional clause that, similar to HAVING, further filters rows from a WHERE clause. The major difference between QUALIFY and HAVING is that with QUALIFY the filtering is based on the result of performing various ordered analytical functions on the data.

QUALIFY Clause Alternative in Oracle

In Oracle, you can use subquery and WHERE clause as QUALIFY clause alternative to filter the result of ordered analytical windows functions.

Following alternative syntax will work on Oracle without any issues.

SELECT StoreID, auto_01
FROM(SELECT StoreID, SUM(profit) OVER (PARTITION BY StoreID) as auto_01,
 SUM(profit) OVER (PARTITION BY StoreID) as rn
   FROM facts) as T
   WHERE rn > 2;

As you can see, windows function in a QUALIFY clause is treated as a column inside the subquery and you can use that column in the WHERE clause of the upper query to filter records.