Re: Analytics/update question

Your code results in the following error:

scott@ORA92> CREATE TABLE your_table
2 (id NUMBER,
3 value NUMBER,
4 date_col DATE)
5 /

Table created.

scott@ORA92> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-mon-YY'
2 /

Session altered.

scott@ORA92> INSERT ALL
2 INTO your_table VALUES (1, -999.99, '26-sep-05')
3 INTO your_table VALUES (1, 16.7, '23-sep-05')
4 INTO your_table VALUES (1, 8.9, '22-sep-05')
5 INTO your_table VALUES (2, 12.7, '23-sep-05')
6 INTO your_table VALUES (2, 12.8, '20-sep-05')
7 INTO your_table VALUES (3, -999.99, '19-sep-05')
8 INTO your_table VALUES (3, 44, '15-sep-05')
9 INTO your_table VALUES (3, 76, '14-sep-05')
10 SELECT * FROM DUAL
11 /

8 rows created.

scott@ORA92> COMMIT
2 /

Commit complete.

scott@ORA92> UPDATE (
2 SELECT id, value, date_col,
3 LAG (value) OVER (PARTITION BY ID ORDER BY date_col, id DESC) AS
lag_val,
4 row_number() OVER (PARTITION BY ID ORDER BY date_col, id DESC) AS
row_num,
5 COUNT(*) OVER (PARTITION BY ID ) AS count_in_group
6 FROM your_table
7 )
8 SET value = lag_val
9 WHERE value = -999.99
10 AND row_num = count_in_group
11 /
UPDATE (
*
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view

But, I see your point, and the following combination works:

scott@ORA92> UPDATE your_table o
2 SET o.value =
3 NVL ((SELECT i.lag_val
4 FROM (SELECT id, value, date_col,
5 LAG (value) OVER
6 (PARTITION BY id
7 ORDER BY date_col) AS lag_val,
8 ROW_NUMBER () OVER
9 (PARTITION BY id ORDER BY date_col) AS row_num,
10 COUNT (*) OVER
11 (PARTITION BY id) AS count_in_group
12 FROM your_table) i
13 WHERE i.id = o.id
14 AND i.value = o.value
15 AND i.date_col = o.date_col
16 AND i.row_num = i.count_in_group), o.value)
17 WHERE o.value = -999.99
18 /

2 rows updated.

scott@ORA92> SELECT * FROM your_table
2 /

ID VALUE DATE_COL
---------- ---------- ---------
1 16.7 26-sep-05
1 16.7 23-sep-05
1 8.9 22-sep-05
2 12.7 23-sep-05
2 12.8 20-sep-05
3 44 19-sep-05
3 44 15-sep-05
3 76 14-sep-05

8 rows selected.

.