I have a table in Teradata that looks like this:
x
Account_Num Install_Due_Dt Install_Num Install_Pay_Dt
12805196 12/08/2019 1 12/08/2019
12805196 10/09/2019 2 ?
12805196 10/10/2019 3 ?
12805196 11/11/2019 4 13/09/2019
12805196 10/12/2019 5 ?
I need to fill the column Install_Pay_Dt with the first not null value. For example, it should look like this:
Account_Num Install_Due_Dt Install_Num Install_Pay_Dt
12805196 12/08/2019 1 12/08/2019
12805196 10/09/2019 2 12/08/2019
12805196 10/10/2019 3 12/08/2019
12805196 11/11/2019 4 13/09/2019
12805196 10/12/2019 5 13/09/2019
I’m using Teradata 15 so I can’t use lag. I’ve been searching a lot but I can’t find a solution. The ID column is Account_Num and the order column is Install_num.
I’ve tried to do something like this:
coalesce(Install_Pay_Dt, MAX(lag_)
OVER(PARTITION BY 1 ORDER BY Install_Num asc
ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)) as lag
But it only fills the second row.
Advertisement
Answer
last_value
is quite similar to lag
, both support the IGNORE NULLS
option
last_value(Install_Pay_Dt IGNORE NULLS)
over (partition by Account_Num
order by Install_Num )