Teradata update recursively

Question

I have data record like this and I want update blank record from previous filled record enter image description here

ID  L_nbr   Code    Descripton
t001    5   S001    ABCDE
t001    12  S002    FGHI
t001    15          JKM
t001    17          NOPE
t001    18  S003    RST
t001    19          UVW
t001    21  S004    ASDS34E
t001    24          GTUS
t001    27          UNIF

Expected data as below

ID  L_nbr   Code    Descripton
t001    5   S001    ABCDE
t001    12  S002    FGHI
t001    15  S002    JKM
t001    17  S002    NOPE
t001    18  S003    RST
t001    19  S003    UVW
t001    21  S004    ASDS34E
t001    24  S004    GTUS
t001    27  S004    UNIF

please help me to acive the above result using Teradata

Thanks

Answer 1

I don't think Aster supports the ignore nulls option on lag(). So, you can do this in two steps. Define the "islands" by using a cumulative sum. Then spread the value around:

select t.*, max(code) over (partition by t_id, grp) as imputed_code
from (select t.*,
             count(code) over (partition by t_id
                               order by l_nbr
                               rows between unbounded preceding and current row
                              ) as grp
      from t
     ) t