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.

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

1
2
3
4
5
6
SELECT
        CUSTOMER_ID
       ,CUSTOMER_NAME
  FROM
       CUSTOMER_T QUALIFY row_number( ) Over( partition BY CUSTOMER_ID ORDER BY POSTAL_CODE DESC ) = 1
;

Output

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
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

1
2
3
4
5
6
7
8
9
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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
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

1
2
3
4
5
6
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

1
2
3
4
5
6
7
8
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.

1
2
3
4
5
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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
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
;
Parent topic: Query Migration Operators