Qualify Function in TERADATA

In Teradata, the QUALIFY clause is specifically used in conjunction with window functions to filter the result set based on the result of those window functions. It allows you to apply conditions to the result of window functions without affecting the overall query logic.

Here's an example to illustrate the usage of QUALIFY in Teradata:

Suppose we have a table called sales with columns product_id, sales_date, and amount. We want to find the total sales amount for each product along with the rank of the product based on the total sales amount. We also want to filter out only those products whose total sales amount is greater than a certain threshold, say $1000.

SELECT 

    product_id,

    SUM(amount) AS total_sales_amount,

    RANK() OVER (ORDER BY SUM(amount) DESC) AS sales_rank

FROM 

    sales

GROUP BY 

    product_id

QUALIFY 

    total_sales_amount > 1000;

In this example:

- We are selecting product_id, the total sales amount (SUM(amount)), and the rank (RANK()) of each product based on the total sales amount.

- We use the GROUP BY clause to group the sales data by product_id.

- The QUALIFY clause filters the results based on the condition total_sales_amount > 1000, ensuring that only products with a total sales amount greater than $1000 are included in the result set.

- The RANK() function assigns a rank to each product based on its total sales amount, ordered in descending order.

This query will return the product IDs, total sales amounts, and ranks of products with total sales amounts greater than $1000 in Teradata.