Select previous row if null

Question

I have the following table in Oracle and I want to fill value that is null in the Activity column with the value of the previous row. How to do using Oracle?

Answer 1

As documentation on LAST_VALUE:

It is an analytic function. It returns the last value in a set of sorted values. If the last value in the set is null, the function returns, to NULL unless you specify IGNORE NULLS. This configuration is useful for data densification. If you specify IGNORE NULLS, then LAST_VALUE returns the non-null value of the handle on set, or NULL if all values are null.
_{Translation via google Translate}

So applying to your case, we have:

SELECT t.activity_no
      ,t.locationcode
      ,t.act_start_date
      ,last_value(t.activity ignore NULLS) over(ORDER BY t.activity_no) somecol
  FROM tabela t

I made an example using oracle Livesql: https://livesql.oracle.com/apex/livesql/file/content_E1EDQGXFCBCHG5GLBCEYS75BZ.html

---

Edition for Oracle 9i:

I found a reply in another forum applied to Oracle 9i, and making some adjustments, and applying to your case, it would look something like this:

SELECT t.activity_no
      ,t.locationcode
      ,t.act_start_date
      ,MAX(CASE
             WHEN t.activity_no = activity_carrydown THEN
              t.activity
           END) over(PARTITION BY activity_carrydown) activity
  FROM (SELECT MAX(CASE
                     WHEN t.activity IS NOT NULL THEN
                      t.activity_no
                   END) over(ORDER BY t.activity_no ASC) activity_carrydown
              ,t.activity_no
              ,t.locationcode
              ,t.act_start_date
              ,t.activity
          FROM tabela t) t