Re: Analytics/update question

From: Barbara Boehmer <baboehme_at_hotmail.com>
Date: 27 Sep 2005 14:54:44 -0700
Message-ID: <1127858084.723612.103200@g43g2000cwa.googlegroups.com>

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

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. Received on Tue Sep 27 2005 - 16:54:44 CDT