Home » SQL & PL/SQL » SQL & PL/SQL » Retain the value from the prev. row / returning clause (merged)
Retain the value from the prev. row / returning clause (merged) [message #216353] Fri, 26 January 2007 15:01 Go to next message
dpong
Messages: 73
Registered: January 2007
Member
I wrote the following anonymous block trying to update ACT_CNT incrementally but it isn't working as desired.
  DECLARE CURSOR C1 IS
  		  SELECT IND_ACTS_ID, IS_FIRST, IS_LAST,
		  LAG(ACT_CNT,1,0) OVER (ORDER BY IND_ACTS_ID) PREV_AC
		  FROM DM_METRICS.DP_IND_ACTS_4
		  FOR UPDATE of ACT_CNT /* NOWAIT */;

		new_ACT_CNT DM_METRICS.DP_IND_ACTS_4.ACT_CNT%TYPE;
  BEGIN
  	   
	   FOR IND_ACTS_REC IN C1 LOOP
	   	   SELECT PREV_AC INTO new_ACT_CNT FROM DM_METRICS.DP_IND_ACTS_4 WHERE IND_ACTS_ID = IND_ACTS_REC.IND_ACTS_ID;
		   
		   IF (new_ACT_CNT >= 1) THEN
		   	  UPDATE DM_METRICS.DP_IND_ACTS_4 SET ACT_CNT = NVL (ACT_CNT, 0) + PREV_AC
			  WHERE CURRENT OF C1;
		   END IF;
	   END LOOP;
	   DBMS_OUTPUT.PUT_LINE('Updated');
  END;
  /




INDIVIDUAL_ID	IND_ACTS_ID	ACT_CNT	IS_FIRST	IS_LAST

56865	1	1	1	0
56865	2		0	0
56865	3		0	0
56865	4		0	0
56865	5		0	0
56865	6		0	0
56865	7		0	0
56865	8		0	0
56865	9		0	0
56865	10		0	0
56865	11		0	0
56865	12		0	0
56865	13		0	0
56865	14		0	0
56865	15		0	0
56865	16		0	0
56865	17		0	0
56865	18		0	0
56865	19		0	0
56865	20		0	0
56865	21		0	0
56865	22		0	0
56865	23		0	0
56865	24		0	0
56865	25		0	0
56865	26		0	0
56865	27		0	0
56865	28		0	0
56865	29		0	0
56865	30		0	0
56865	31		0	0
56865	32		0	0
56865	33		0	0
56865	34		0	0
56865	35		0	0
56865	36		0	0
56865	37		0	0
56865	38		0	0
56865	39		0	0
56865	40		0	0
56865	41		0	0
56865	42		0	0
56865	43		0	0
56865	44		0	0
56865	45		0	0
56865	46		0	0
56865	47		0	0
56865	48		0	1
103639	49	1	1	0
103639	50		0	0
103639	51		0	1
147380	52	1	1	0



Could someone please point out why my loop isn't working?
Re: retain the value from the prev row [message #216356 is a reply to message #216353] Fri, 26 January 2007 15:09 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
>but it isn't working as desired.
We (TINW) are NOT mind readers!
How are we supposed to know what is the desired output??????????
Since I don't see any errors, it appears to be working OK to me.
Re: retain the value from the prev row [message #216360 is a reply to message #216356] Fri, 26 January 2007 15:16 Go to previous messageGo to next message
dpong
Messages: 73
Registered: January 2007
Member
So basically I wanted the act_cnt to be updated in an incrementally sequential way.

Something like the following. Let me know if you still have doubts as to what I wanted to achieve. This exactly works in the same principle as the
retain statement &&
ACT_CNT = ACT_CNT + 1;
in SAS.

INDIVIDUAL_ID	IND_ACTS_ID	ACT_CNT	IS_FIRST	IS_LAST

56865	1	1	1	0
56865	2	[B]2[/B]	0	0
56865	3	[B]3[/B]	0	0
56865	4	[B]4[/B]	0	0
56865	5	[B]5[/B]	0	0
56865	6	[B]6[/B]	0	0
56865	7		0	0
56865	8		0	0
56865	9		0	0
56865	10		0	0
56865	11		0	0
56865	12		0	0
56865	13		0	0
56865	14		0	0
56865	15		0	0
56865	16		0	0
56865	17		0	0
56865	18		0	0
56865	19		0	0
56865	20		0	0
56865	21		0	0
56865	22		0	0
56865	23		0	0
56865	24		0	0
56865	25		0	0
56865	26		0	0
56865	27		0	0
56865	28		0	0
56865	29		0	0
56865	30		0	0
56865	31		0	0
56865	32		0	0
56865	33		0	0
56865	34		0	0
56865	35		0	0
56865	36		0	0
56865	37		0	0
56865	38		0	0
56865	39		0	0
56865	40		0	0
56865	41		0	0
56865	42		0	0
56865	43		0	0
56865	44		0	0
56865	45		0	0
56865	46		0	0
56865	47		0	0
56865	48		0	1
103639	49	1	1	0
103639	50	[B]2[/B]	0	0
103639	51	[B]3[/B]	0	1
147380	52	1	1	0

Re: retain the value from the prev row [message #216363 is a reply to message #216353] Fri, 26 January 2007 15:36 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
Do you realize that there is a difference between SQL & PL/SQL?
Do you realize that you posted PL/SQL code to a SQL Expert forum?
I do not see where you are retaining the previous record value in any variable.
I do not see where any incrementing is being done. VAL := VAL + 1; so of course the results are not what you desire.

[Updated on: Fri, 26 January 2007 15:37] by Moderator

Report message to a moderator

Re: retain the value from the prev row [message #216364 is a reply to message #216363] Fri, 26 January 2007 15:44 Go to previous messageGo to next message
dpong
Messages: 73
Registered: January 2007
Member
Sorry, please calm down and stop being furious on your responses.

UPDATE ACT_CNT = NVL(ACT_CNT, 0) + PREV_AC should be the incremental statement. Let me know if you've any questions.

I really understood this is a PL/SQL question. SOrry I am a newbie and I'd tried to find a way to move this message to the correct forum. But I'll leave this job to the administrator... and that I don't want to do double posting to waste resources.
THANKS!!!
Re: retain the value from the prev row [message #216369 is a reply to message #216353] Fri, 26 January 2007 16:22 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
The records returned by your SELECT will be in an indeterminate order.
So what is the "preceeding record" during 1 invocation may not be the same record during the next invocation.

[Updated on: Fri, 26 January 2007 16:22] by Moderator

Report message to a moderator

Re: retain the value from the prev row [message #216370 is a reply to message #216369] Fri, 26 January 2007 16:27 Go to previous messageGo to next message
dpong
Messages: 73
Registered: January 2007
Member
So do you have any solution to it? Since SQL doesn't work quite seamlessly longitudinally, the only way I could think of was to link the records using aliases of the tables. But since I'm already in a CURSOR, what would be the correct and/or plausible way [syntax] to add in the where clause a.IND_ACTS_ID = b.IND_ACTS_ID ?


Re: retain the value from the prev row [message #216372 is a reply to message #216353] Fri, 26 January 2007 16:32 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
The most common way to ensure rows are presented consistent "order" is to user the ORDER BY clause on the SELECT staement & specifying the column(s) which produce the desired & ordered results.
Re: retain the value from the prev row [message #216381 is a reply to message #216372] Fri, 26 January 2007 23:14 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
without create table and insert statement it is difficult to answer. However see if the following merge clause helps you



SQL> merge into DM_METRICS.DP_IND_ACTS_4 a

using (
SELECT IND_ACTS_ID, IS_FIRST, IS_LAST, individual_id
		  row_number() over (partition by individual_id order by IND_ACTS_ID) act_cnt
		  FROM DM_METRICS.DP_IND_ACTS_4
) b

on (a.individual_id = b.individual_id)
when matched then set a.ACT_CNT = b.act_cnt
when not matched then -- assuming individual id is not null then this condition will never be true therefore setting dummy insert or incase of 10G you can completely remove not matched condition
insert (individual_id) values(b.individual_id)
/



[Updated on: Fri, 26 January 2007 23:28]

Report message to a moderator

Re: retain the value from the prev row [message #216439 is a reply to message #216381] Sat, 27 January 2007 14:42 Go to previous messageGo to next message
dpong
Messages: 73
Registered: January 2007
Member
the "set a.ACT_CNT = b.ACT_CNT" statement isn't working.
I guess the error suggest set isn't the keyword it's expecting.

I've never seen merge into in Oracle SQL b4. Thanks for showing me. Another thing I notice is since individual_id isn't the unique id, I would say the constraint should be (a.individual_id = b.individual_id AND a.IND_ACTS_ID = b.IND_ACTS_ID)

Very clever of you to remind me of row_number() are in the arsenals.

But yes, it's still not working as of yet.
restrictions on RETURNING CLAUSE [message #216442 is a reply to message #216353] Sat, 27 January 2007 16:24 Go to previous messageGo to next message
dpong
Messages: 73
Registered: January 2007
Member
CREATE TABLE DM_METRICS.DP_IND_ACTS_TEST_1 as
	   SELECT * FROM DM_METRICS.DP_IND_ACTS_4 ;

  DECLARE CURSOR C1 IS
  		  SELECT IND_ACTS_ID, IS_FIRST, IS_LAST,
		  LAG(ACT_CNT,1,0) OVER (ORDER BY IND_ACTS_ID) PREV_AC
		  FROM DM_METRICS.DP_IND_ACTS_TEST_1
		  FOR UPDATE of ACT_CNT  NOWAIT;

		new_ACT_CNT DM_METRICS.DP_IND_ACTS_TEST_1.ACT_CNT%TYPE;
  BEGIN
  	   
	   FOR IND_ACTS_REC IN C1 LOOP
	   	   SELECT PREV_AC INTO new_ACT_CNT FROM DM_METRICS.DP_IND_ACTS_TEST_1 WHERE IND_ACTS_ID = IND_ACTS_REC.IND_ACTS_ID;
		   
		   IF (new_ACT_CNT >= 1) THEN
		   	  UPDATE DM_METRICS.DP_IND_ACTS_TEST_1 SET ACT_CNT = NVL (ACT_CNT, 0) + PREV_AC
			  WHERE CURRENT OF C1 RETURNING ACT_CNT INTO PREV_AC
			  
		   END IF;
	   END LOOP;
	   DBMS_OUTPUT.PUT_LINE('Updated');
  END;
  /  


Does anyone know why there is a limitation as to when I use RETURNING in update ?
It seems to me when cursor is used, returning doesn't work.

Here are the error messages, if you find them useful

Error on line 0
DECLARE CURSOR C1 IS
SELECT IND_ACTS_ID, IS_FIRST, IS_LAST,
LAG(ACT_

ORA-06550: line 15, column 26:
PL/SQL: ORA-00933: SQL command not properly ended
ORA-06550: line 14, column 9:
PL/SQL: SQL Statement ignored
ORA-06550: line 18, column 9:
PLS-00103: Encountered the symbol "LOOP" when expecting one of the following:

if
ORA-06550: line 21, column 0:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

end not pragma final instantiable order overriding static
member constructor map
Re: restrictions on RETURNING CLAUSE [message #216443 is a reply to message #216442] Sat, 27 January 2007 16:37 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Your last question regards the same problem as you've had previously. Please, do not open new topic for the existing problem; continue discussion in existing one.
Re: restrictions on RETURNING CLAUSE [message #216444 is a reply to message #216443] Sat, 27 January 2007 17:20 Go to previous messageGo to next message
dpong
Messages: 73
Registered: January 2007
Member
almost the same code but different subject of issue. Actually I wanted my thread of messages be moved to the advanced forum. That's my original thought.

Thanks!
Re: Retain the value from the prev. row / returning clause (merged) [message #216445 is a reply to message #216353] Sat, 27 January 2007 18:23 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
>Does anyone know why there is a limitation as to when I use RETURNING in update ?
PEBKAC
>It seems to me when cursor is used, returning doesn't work.
Of course it doesn't work when used incorrectly.

The fine SQL Reference Manual says:
http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_10007.htm#i2126358

returning_clause

The returning clause retrieves the rows affected by a DML statement. You can specify this clause for tables and materialized views and for views with a single base table.

When operating on a single row, a DML statement with a returning_clause can retrieve column expressions using the affected row, rowid, and REFs to the affected row and store them in host variables or PL/SQL variables.

& you have not coded as specified above.
Re: Retain the value from the prev. row / returning clause (merged) [message #216449 is a reply to message #216445] Sun, 28 January 2007 01:41 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
@anacedent:

Since you quite explicitly point the OP to the documentation, maybe you could quote the piece where it says that the returning clause does not seem to work in conbination with the where current of?
I cannot find it; it is NOT in the Restrictions section of the returning clause.

SQL> create table faq (id number, text varchar2(100));

Table created.

SQL> insert into faq (id, text) values (1, 'One');

1 row created.

SQL> insert into faq (id, text) values (2, 'Two');

1 row created.


What is the difference (according to the piece of documentation you refer to) between
SQL> declare
  2    cursor c
  3    is
  4  	 select faq.text
  5  	 from	faq
  6  	 where	id = 1
  7  	 for	update of text nowait
  8    ;
  9  
 10    l_newtext faq.text%type;
 11  begin
 12    for r in c
 13    loop
 14  	 update faq
 15  	 set	text = text||' updated'
 16  	 --where  current of c
 17  	 where	id = 1
 18  	 returning text into l_newtext
 19  	 ;
 20  
 21  	 dbms_output.put_line('New text: '||l_newtext);
 22    end loop;
 23  end;
 24  /
New text: One updated

PL/SQL procedure successfully completed.

and
SQL> declare
  2    cursor c
  3    is
  4  	 select faq.text
  5  	 from	faq
  6  	 where	id = 1
  7  	 for	update of text nowait
  8    ;
  9  
 10    l_newtext faq.text%type;
 11  begin
 12    for r in c
 13    loop
 14  	 update faq
 15  	 set	text = text||' updated'
 16  	 where	current of c
 17  	 returning text into l_newtext
 18  	 ;
 19  
 20  	 dbms_output.put_line('New text: '||l_newtext);
 21    end loop;
 22  end;
 23  /
    returning text into l_newtext
    *
ERROR at line 17:
ORA-06550: line 17, column 5:
PL/SQL: ORA-00933: SQL command not properly ended
ORA-06550: line 14, column 5:
PL/SQL: SQL Statement ignored
Re: Retain the value from the prev. row / returning clause (merged) [message #216450 is a reply to message #216449] Sun, 28 January 2007 03:28 Go to previous messageGo to next message
dpong
Messages: 73
Registered: January 2007
Member
It should be in the CURSOR restriction section, as I recalled..
Re: Retain the value from the prev. row / returning clause (merged) [message #216468 is a reply to message #216353] Sun, 28 January 2007 12:34 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
By RTFM I learned something new.
http://download-west.oracle.com/docs/cd/B19306_01/appdev.102/b14261/collections.htm#BABHDGIG

Restrictions on Record Inserts and Updates

Currently, the following restrictions apply to record inserts/updates:

*

Record variables are allowed only in the following places:
o

On the right side of the SET clause in an UPDATE statement
o

In the VALUES clause of an INSERT statement
o

In the INTO subclause of a RETURNING clause

Record variables are not allowed in a SELECT list, WHERE clause, GROUP BY clause, or ORDER BY clause.
*

The keyword ROW is allowed only on the left side of a SET clause. Also, you cannot use ROW with a subquery.
*

In an UPDATE statement, only one SET clause is allowed if ROW is used.
*

If the VALUES clause of an INSERT statement contains a record variable, no other variable or value is allowed in the clause.
*

If the INTO subclause of a RETURNING clause contains a record variable, no other variable or value is allowed in the subclause.
*

The following are not supported:
o

Nested record types
o

Functions that return a record
o

Record inserts and updates using the EXECUTE IMMEDIATE statement.
Re: Retain the value from the prev. row / returning clause (merged) [message #216473 is a reply to message #216468] Sun, 28 January 2007 15:59 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Still no answer that convinces me. Also in this quote, there is nothing about the combination of the where current of clause and the returning clause...
My example has nothing to do with collections.

[Updated on: Sun, 28 January 2007 16:01]

Report message to a moderator

Re: Retain the value from the prev. row / returning clause (merged) [message #216475 is a reply to message #216353] Sun, 28 January 2007 16:10 Go to previous messageGo to next message
srinivnp
Messages: 136
Registered: January 2006
Location: stlouis MO USA
Senior Member

If you want to do an " update in an incrementally sequential way" , as per the requirement , the merge solution from bonker should work.

Only thing is that the sequential order should be repeatable.

Meaning the columns that determine the sequential order should be unique.


------




SQL> select * from foo;

NAME SEQNO
------------------------------ ----------
PROCESSES
PROGRAMS
USERS
USER_AUTHORITIES
BOX_TYPE
CARRIERS
CARRIER_DIM_WEIGHT
CARRIER_RATES
METHOD

9 rows selected.


SQL>
Quote:
merge into foo
using
(select name,row_number() over (order by name) rn from foo )
src
on (foo.name=src.name)
when matched then update
set
foo.seqno=src.rn;


9 rows merged.

SQL> select * from foo order by name;

NAME SEQNO
------------------------------ ----------
BOX_TYPE 1
CARRIERS 2
CARRIER_DIM_WEIGHT 3
CARRIER_RATES 4
METHOD 5
PROCESSES 6
PROGRAMS 7
USERS 8
USER_AUTHORITIES 9

9 rows selected.


Here column name is unique and the ordering is on that column.


Srini
Re: Retain the value from the prev. row / returning clause (merged) [message #216485 is a reply to message #216475] Sun, 28 January 2007 21:58 Go to previous messageGo to next message
dpong
Messages: 73
Registered: January 2007
Member
Right. I tested it at work with the requirement that the constraint has to be on a set of unique id's, which I stressed earlier in the thread.

i.e. a.inidivudal_id = b.individiual_id
AND a.IND_ACTS_ID = b.IND_ACTS_ID

but yes, this is solved using some of the functions SQL has already in store. But NOTHING using the keyword CURRENT OF and CURSOR. So I'd say this problem might be a little bit too advanced for non-PL/SQL programmers.


Re: restrictions on RETURNING CLAUSE [message #216572 is a reply to message #216442] Mon, 29 January 2007 08:03 Go to previous messageGo to next message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
dpong wrote on Sat, 27 January 2007 17:24


Error on line 0
DECLARE CURSOR C1 IS
SELECT IND_ACTS_ID, IS_FIRST, IS_LAST,
LAG(ACT_

ORA-06550: line 15, column 26:
PL/SQL: ORA-00933: SQL command not properly ended
ORA-06550: line 14, column 9:
PL/SQL: SQL Statement ignored
ORA-06550: line 18, column 9:
PLS-00103: Encountered the symbol "LOOP" when expecting one of the following:

if
ORA-06550: line 21, column 0:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

end not pragma final instantiable order overriding static
member constructor map



Seems to me maybe you are using 8i which did not support Analytics in PL/SQL.
Additinally, asking for CREATE TABLE and INSERT statements is to help us know what structure you are working with. Giving a CTAS is of no help.
Re: restrictions on RETURNING CLAUSE [message #216605 is a reply to message #216572] Mon, 29 January 2007 12:03 Go to previous messageGo to next message
dpong
Messages: 73
Registered: January 2007
Member
Right. I understand that would help you guys in terms of testing. But I don't create the table. Let me give you DESC later when I have some time. Thanks.
Re: restrictions on RETURNING CLAUSE [message #216610 is a reply to message #216605] Mon, 29 January 2007 12:29 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Why don't YOU rewrite the desc to a table creation script instead of leaving that to the people who want to provide you with answers?
Previous Topic: Number Truncation in DB Link
Next Topic: syntax loop error
Goto Forum:
  


Current Time: Wed Dec 07 20:03:36 CST 2016

Total time taken to generate the page: 0.14205 seconds