QUALIFY ROW_NUMBER in teradata

Question

below is the oracle SQL and i want to change it in Teradata format.

SELECT branch_code,
    branch_no,
    c_no,
    cd_type
FROM (
    SELECT branch_code,
        branch_no,
        c_no,
        cd_type,
        * * RANK() OVER (
            PARTITION BY c_no ORDER BY cd_type
            ) RANK * *
    FROM (
        SELECT branch_code,
            branch_no,
            c_no,
            MIN(cd_type) cd_type
        FROM EMPLOYEE
        WHERE S_CODE = 'C'
            AND (branch_no) NOT IN (
                SELECT branch_code
                FROM DEPARTMENT
                WHERE branch_code = 'ABC'
                )
        )
    )
WHERE RANK = 1

I have used QUALIFY for RANK as below .

SELECT branch_code,
    branch_no,
    c_no,
    cd_type
FROM (
    SELECT branch_code,
        branch_no,
        c_no,
        cd_type,
        * * QUALIFY ROW_NUMBER() OVER (
            PARTITION BY c_no ORDER BY cd_type
            ) * * RANK
    FROM (
        SELECT branch_code,
            branch_no,
            c_no,
            MIN(cd_type) cd_type
        FROM EMPLOYEE
        WHERE S_CODE = 'C'
            AND (branch_no) NOT IN (
                SELECT branch_code
                FROM DEPARTMENT
                WHERE branch_code = 'ABC'
                )
        )
    )
WHERE RANK = 1

But getting error that "Expected something between , and QUALIFY.

Can we put QUALIFY in select statement ?

Solution 1

You probably simplified your existing query as this is no valid Oracle SQL (there's no GROUP BY in the Inline View). Plus you compare branch_no to branch_codein the NOT IN.

Otherwise Ed Gibbs' answer can be further simplified to:

SELECT branch_code,
    branch_no,
    c_no,
    MIN(cd_type) cd_type
FROM EMPLOYEE
WHERE S_CODE = 'C'
    AND (branch_no) NOT IN (
        SELECT branch_no
        FROM DEPARTMENT
        WHERE branch_code = 'ABC'
        )
GROUP BY branch_code,
    branch_no,
    c_no
QUALIFY
   RANK()
   OVER (PARTITION BY c_no 
         ORDER BY MIN(cd_type) = 1

If branch_no is defined as NULLable you should also rewrite NOT IN to NOT EXISTS

Solution 2

Normally, analytic values like RANK are calculated second to last, after joining and filtering and GROUP BY and HAVING. The only thing done after analytic values is ORDER BY. That's why in Oracle you need to put the RANK into an inner query and then test its value in an outer query. In Teradata, QUALIFY is executed after the analytic functions and before the ORDER BY, meaning you don't need the outer query to test the RANK value.

I don't have access to Teradata today, so this query isn't tested but I like to think it's close:

SELECT branch_code,
    branch_no,
    c_no,
    cd_type
FROM (
    SELECT branch_code,
        branch_no,
        c_no,
        MIN(cd_type) cd_type
    FROM EMPLOYEE
    WHERE S_CODE = 'C'
        AND (branch_no) NOT IN (
            SELECT branch_code
            FROM DEPARTMENT
            WHERE branch_code = 'ABC'
        )
)
QUALIFY ROW_NUMBER() OVER (PARTITION BY c_no ORDER BY cd_type) = 1