Re: Analytics/update question

Barbara Boehmer schrieb:
> Your code results in the following error:
>
> scott_at_ORA92> CREATE TABLE your_table
> 2 (id NUMBER,
> 3 value NUMBER,
> 4 date_col DATE)
> 5 /
>
> Table created.
>
> scott_at_ORA92> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-mon-YY'
> 2 /
>
> Session altered.
>
> scott_at_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_at_ORA92> COMMIT
> 2 /
>
> Commit complete.
>
> scott_at_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

You are correct, i was not aware of this restriction regarding analytical functions in update(subquery)

>
> But, I see your point, and the following combination works:
>
> scott_at_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_at_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.
>

Best regards

Maxim Received on Tue Sep 27 2005 - 20:57:45 CDT