QUALIFY¶
In general, the QUALIFY clause is accompanied by analytic functions (window functions) such as CSUM(), MDIFF(), ROW_NUMBER() and RANK(). This is addressed using sub-query that contains the window functions specified in the QUALIFY clause. Migration tools support QUALIFY with MDIFF(), RANK() and ROW_NUMBER(). QUALIFY is a Teradata extension and not an ANSI standard syntax. It is executed after the WHERE and GROUP BY clauses. QUALIFY must start in new line.
Note
Migration tools support column name and/or expressions in the ORDER BY clause only if the column name and/or expression is explicitly included in the SELECT statement as well.
Input: QUALIFY
SELECT
CUSTOMER_ID
,CUSTOMER_NAME
FROM
CUSTOMER_T QUALIFY row_number( ) Over( partition BY CUSTOMER_ID ORDER BY POSTAL_CODE DESC ) = 1
;
Output
SELECT
CUSTOMER_ID
,CUSTOMER_NAME
FROM
(
SELECT
CUSTOMER_ID
,CUSTOMER_NAME
,row_number( ) Over( partition BY CUSTOMER_ID ORDER BY POSTAL_CODE DESC ) AS ROW_NUM1
FROM
CUSTOMER_T
) Q1
WHERE
Q1.ROW_NUM1 = 1
;
Input: QUALIFY with MDIFF and RANK
SELECT
material_name
,unit_of_measure * standard_cost AS tot_cost
FROM
raw_material_t m LEFT JOIN supplies_t s
ON s.material_id = m.material_id
QUALIFY rank ( ) over( ORDER BY tot_cost DESC ) IN '5'
OR mdiff( tot_cost ,3 ,material_name ) IS NULL
;
Output
SELECT
material_name
,tot_cost
FROM
(
SELECT
material_name
,unit_of_measure * standard_cost AS tot_cost
,rank ( ) over( ORDER BY unit_of_measure * standard_cost DESC ) AS ROW_NUM1
,unit_of_measure * standard_cost - (LAG( unit_of_measure * standard_cost ,3 ,NULL ) over( ORDER BY material_name )) AS ROW_NUM2
FROM
raw_material_t m LEFT JOIN supplies_t s
ON s.material_id = m.material_id
) Q1
WHERE
Q1.ROW_NUM1 = '5'
OR Q1.ROW_NUM2 IS NULL
;
Input: QUALIFY with ORDER BY having columns that do not exist in the SELECT list
SELECT Postal_Code
FROM db_pvfc9_std.Customer_t t1
GROUP BY Customer_Name ,Postal_Code
QUALIFY ---comments
( Rank ( CHAR(Customer_Address) DESC ) ) = 1
ORDER BY t1.Customer_Name;
Output
SELECT Postal_Code FROM
( SELECT Customer_Name, Postal_Code
, Rank () over( PARTITION BY Customer_Name, Postal_Code ORDER BY LENGTH(Customer_Address) DESC ) AS Rank_col
FROM db_pvfc9_std.Customer_t t1
) Q1
WHERE /*comments*/
Q1.Rank_col = 1
ORDER BY Q1.Customer_Name;
Input: QUALIFY with COLUMN ALIAS - the corresponding column expression should not be added again in SELECT list.
SELECT material_name, unit_of_measure * standard_cost as tot_cost,
RANK() over(order by tot_cost desc) vendor_cnt
FROM raw_material_t m left join supplies_t s
ON s.material_id = m.material_id
QUALIFY vendor_cnt < 5 or MDIFF(tot_cost, 3, material_name) IS NULL;
Output
SELECT material_name, tot_cost, vendor_cnt
FROM ( SELECT material_name
, unit_of_measure * standard_cost AS tot_cost
, rank () over (ORDER BY tot_cost DESC) vendor_cnt
, tot_cost - ( LAG(tot_cost ,3 ,NULL) over (ORDER BY material_name) ) AS anltfn
FROM raw_material_t m LEFT JOIN supplies_t s
ON s.material_id = m.material_id
) Q1
WHERE Q1.vendor_cnt < 5 OR Q1.anltfn IS NULL
;
TITLE with QUALIFY¶
Input
REPLACE VIEW ${STG_VIEW}.LP06_BMCLIINFP${v_Table_Suffix_Inc}
(
CLICLINBR
, CLICHNNAM
, CLICHNSHO
, CLICLIMNE
, CLIBNKCOD
)
AS
LOCKING ${STG_DATA}.LP06_BMCLIINFP${v_Table_Suffix_Inc} FOR ACCESS
SELECT
CLICLINBR (title ' VARCHAR(20)')
, CLICHNNAM (title ' VARCHAR(200)')
, CLICHNSHO (title ' VARCHAR(20)')
, CLICLIMNE (title ' VARCHAR(10)')
, CLIBNKCOD (title ' VARCHAR(11)')
FROM
${STG_DATA}.LP06_BMCLIINFP${v_Table_Suffix_Inc} s1
QUALIFY
ROW_NUMBER() OVER(PARTITION BY CLICLINBR ORDER BY CLICLINBR ) = 1
;
Output
CREATE OR REPLACE VIEW ${STG_VIEW}.LP06_BMCLIINFP${v_Table_Suffix_Inc}
(
CLICLINBR
, CLICHNNAM
, CLICHNSHO
, CLICLIMNE
, CLIBNKCOD
)
AS
/* LOCKING ${STG_DATA}.LP06_BMCLIINFP${v_Table_Suffix_Inc} FOR ACCESS */
SELECT CLICLINBR
, CLICHNNAM
, CLICHNSHO
, CLICLIMNE
, CLIBNKCOD
FROM (
SELECT
CLICLINBR /* (title ' VARCHAR(20)') */
, CLICHNNAM /* (title ' VARCHAR(200)') */
, CLICHNSHO /* (title ' VARCHAR(20)') */
, CLICLIMNE /* (title ' VARCHAR(10)') */
, CLIBNKCOD /* (title ' VARCHAR(11)') */
, ROW_NUMBER() OVER(PARTITION BY CLICLINBR ORDER BY CLICLINBR ) AS ROWNUM1
FROM
${STG_DATA}.LP06_BMCLIINFP${v_Table_Suffix_Inc} s1 ) Q1
WHERE Q1.ROWNUM1 = 1
;