Teradata Qualify Function Not Working Correctly

Question

to put it simply I have table a which looks like...

ID   |  Start_Date      
1234 |  9/1/2017

table b has payment dates and amounts that correspond to the ID from table a. table b looks like..

ID   |  Recd_Date  |  Tran_Code  | Total_Recd  
1234 |  10/2/2017  |    173      | $100
1234 |  11/1/2017  |    173      | $200

I'm trying to use the Qualify function to show the first recd_date and amount from table b that occurred after the Start_Date from table a. So far I have the following code below. the code works but for some reason it shows the 11/1/2017.

SELECT 
a.ID,
a.Start_Date,
b.Recd_Dt,
b.Total_Recd,
b.Tran_ID
FROM
(SELECT ID, START_DATE FROM tbl) a
LEFT JOIN 
(SELECT ID, Recd_Dt, tran_ID, Total_Recd
FROM tbl b
WHERE Tran_ID = '173' AND Total_Recd > 0
QUALIFY ROW_NUMBER() OVER(PARTITION BY ID ORDER BY Recd_Dt)=1
)b
ON a.ID = b.ID AND b.Recd_Dt >= a.Start_Date

Where a.ID = '1234'

the code runs, but for some reason I am showing null values for the b table columns. If I change the Qualify line to..

QUALIFY ROW_NUMBER() OVER(PARTITION BY ID ORDER BY Recd_Dt ASC)=1

I still get a null value, If I change it to DESC order I get the 11/1/2017 entry.

What do I need to do to get the 10/2/2017 line?

You're using a LEFT JOIN, are you certain that there are any matching records for the cases where you get NULLs in b? (Note that the join predicate b.Recd_Dt >= a.Start_Date is applied after the ROW_NUMBER() qualification...) — MatBailie
Can you elaborate on the number of rows and the number of rows per ID in both tables? And answer the question on your previous post: Is it possible to have additional rows in table a for the same ID? If yes, how to deal with those? — dnoeth

MatBailie MatBailie · Accepted Answer · 2017-11-21T20:53:26

You're applying the QUALIFY before the LEFT JOIN. This means that you're finding one record in the table (per id), then joining that on to a.

Apply the QUALIFY after the LEFT JOIN...

SELECT 
    a.ID,
    a.Start_Date,
    b.Recd_Dt,
    b.Total_Recd,
    b.Tran_ID
FROM
(
    SELECT ID, START_DATE FROM tbl
)
    a
LEFT JOIN 
(
    SELECT ID, Recd_Dt, tran_ID, Total_Recd
    FROM tbl b
    WHERE Tran_ID = '173' AND Total_Recd > 0
)
    b
        ON  b.ID       = a.ID
        AND b.Recd_Dt >= a.Start_Date
WHERE
    a.ID = '1234'
QUALIFY
    ROW_NUMBER() OVER(PARTITION BY a.ID ORDER BY b.Recd_Dt) = 1

Teradata Qualify Function Not Working Correctly

1 Answers