QUALIFY Clause in Synapse and TSQL- Alternative and Examples

  • Post author:
  • Post last modified:February 26, 2021
  • Post category:Azure Synapse
  • Reading time:4 mins read

The Azure Synapse data warehouse is based on the Microsoft TSQL. If you are migrating from Teradata to Azure Synapse, you will notice that many features are missing in the Synapse date warehouse. The feature such as QUALIFY clause is widely used in Teradata. But, the same feature is not available in Synapse. In this article, we will check what is QUALIFY Clause alternative in Azure Synapse and TSQL.

QUALIFY Clause in Azure Synapse and TSQL

The relational databases such as Teradata uses QUALIFY clause to filter the result of ordered analytical function. Azure Synapse and TSQL do not support this feature yet. But, there is a recommended approach is available in the official documentation. The approach is to use ROW_NUMBER analytic function and filter out data based on row numbers.

Before jumping into alternative approach, let us first check little bit about QUALIFY clause.

QUALIFY Clause

A QUALIFY is a conditional clause in the SELECT statement that filters results of a previously computed ordered analytical function according to 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 Synapse and TSQL

In Azure Synapse and TSQL, 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 Synapse and TSQL 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, you can define a window function in a QUALIFY clause as a column inside the subquery and you can use that column in the WHERE clause of the upper query to filter records.

Related Articles,

Hope this helps 🙂

Tags: , ,