CODEWITHSUNDEEP
sqlteradata
nbwest76
nbwest76

Reputation: 59

Teradata Qualify Function Not Working Correctly

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?

Upvotes: 0

Views: 1549

Answers (1)

MatBailie
MatBailie

Reputation: 86775

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

Upvotes: 1

Related Questions