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?
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