Select the previous line if it is null

2

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 it using Oracle?

    
asked by anonymous 24.05.2017 / 16:38

1 answer

3

According to documentation on LAST_VALUE :

  

It is an analytical function. It returns the last value in a set of   values. If the last value in the set is null, the   returns, to NULL unless you specify IGNORE NULLS. It is   configuration is useful for data densification. If you specify   IGNORE NULLS, then LAST_VALUE returns the non-null value of the handle in the   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: link

Oracle 9i Edition:

I found a answer in another forum applied to Oracle 9i , and making some adjustments, and applying to your case, 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
    
24.05.2017 / 20:16