CODEWITHSUNDEEP
sqlteradata
Pablo
Pablo

Reputation: 140

Teradata SQL getting last not null value

I have a table in Teradata that looks 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              ?
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.

Upvotes: 0

Views: 2552

Answers (2)

dnoeth
dnoeth

Reputation: 60462

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 )

Upvotes: 1

ravioli
ravioli

Reputation: 3823

I'm sure there's a cleaner way to do this, but here's one option:

SELECT 
  acct.Account_Num, 
  acct.Install_Due_Dt, 
  acct.Install_Num, 
  COALESCE(
    acct.Install_Pay_Dt, -- Use original value, if not null
    dt.Install_Pay_Dt_Default -- Use default value if original is null
  ) AS Install_Pay_Dt
FROM MyTable acct
LEFT JOIN (
  SELECT Install_Pay_Dt AS Install_Pay_Dt_Default
  FROM MyTable
  WHERE Install_Pay_Dt IS NOT NULL -- Only get non-NULL values
  QUALIFY ROW_NUMBER() OVER(ORDER BY Install_Num) = 1 -- Only get first row
) dt ON 1=1 -- LEFT JOIN instead of CROSS JOIN in case there are no "install_pay_dt" values

This assumes you want to apply the "default value" logic to all rows as a single group, which is what your posted query suggests to me as PARTITION BY 1 will process all rows in a single partition. If you want to apply the logic by account_num groups of rows, then you'd have to modify it a bit.

Upvotes: 0

Related Questions