user1723699 user1723699 - 2 years ago 163
SQL Question

Fill null values with last non-null amount - Oracle SQL

I have a table that has 4 columns: Item, Year, Month, Amount. Some of the values for Amount are null and when that happens I want to fill those values in with the previous Amount value that is not null. I can easily do this with the LAG function when there is only one null value but when there are multiple in a row I am not sure how to approach it. Below is an example of what the table might look like with an added column for what I want to add in my query:

Item | Year | Month | Amount | New_Amount
AAA | 2013 | 01 | 100 | 100
AAA | 2013 | 02 | | 100
AAA | 2013 | 03 | 150 | 150
AAA | 2013 | 04 | 125 | 125
AAA | 2013 | 05 | | 125
AAA | 2013 | 06 | | 125
AAA | 2013 | 07 | | 125
AAA | 2013 | 08 | 175 | 175

I had two ideas which I can't seem to get to work to produce what I want. First I was going to use LAG but then I noticed when there are multiple null values in a row it won't satisfy that. Next I was going to use FIRST_VALUE but that wouldn't help in this situation where there is a null followed by values followed by more nulls. Is there a way to use FIRST_VALUE or another similar function to retrieve the last non-null value?

Answer Source

last_value with IGNORE NULLS works fine in Oracle 10g:

select item, year, month, amount, 
       last_value(amount ignore nulls) 
         over(partition by item 
              order by year, month 
              rows between unbounded preceding and 1 preceding) from tab;

rows between unbounded preceding and 1 preceding sets the window for analytic function.

In this case Oracle is searching for LAST_VALUE inside the group defined in PARTITION BY (the same item) from the begining (UNBOUNDED PRECEDING) until current row - 1 (1 PRECEDING)

It's a common replacement for LEAD/LAG with IGNORE NULLS in Oracle 10g

However, if you're using Oracle 11g you can use LAG from the Gordon Linoff's answer (there is a small typo with "ignore nulls")

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download