| Update Table with cursor (merged) [message #477953] |
Tue, 05 October 2010 06:23  |
kame
Messages: 69
Registered: July 2009
|
Member |
|
|
I have made changes in my form
1st step:-
for Inserting records created below cursor, records are inserted and I can see on my screen (form) also.
DECLARE
CURSOR Invest IS
select * from Tempinvest;
BEGIN
OPEN Invest;
GO_BLOCK('ReturnReport');
last_record;
create_record;
LOOP
FETCH Invest INTO :ReturnReport.code,
:ReturnReport.inv_date,
:ReturnReport.inv_co_code,
:ReturnReport.inv_co_name,
:ReturnReport.inv_fnd_code,
:ReturnReport.inv_fnd_name,
:ReturnReport.inv_nofu,
:ReturnReport.inv_amount,
:ReturnReport.inv_uprice,
:ReturnReport.Tavg,
:returnReport.srno;
com;
EXIT WHEN Invest%NOTFOUND;
NEXT_RECORD;
END LOOP;
-- FIRST_RECORD;
CLOSE Invest;
END;
2nd step:-
Updating records
I wan to update some columns in this table so I created a 2nd cursor to update records but this cursor is not working accordingly.
my requirement is: before Update first of all Find inv_co_code & inv_fnd_code when find then update column Redunits
2nd Cursor
DECLARE
ddate date; refdate date; co_code number; co_name varchar2(50); fnd_code number; fnd_name varchar2(50); units number; amount number; stat varchar2(1);
CURSOR bnr IS
select Ddate,refdate,co_code,co_name,fnd_code,fnd_name,units,amount,stat from (
select inv_date DDate,Vdate RefDate,inv_co_code Co_code,inv_co_name Co_name,inv_fnd_code Fnd_code,inv_fnd_name Fnd_name,
inv_nofu Units,inv_amount Amount,Status Stat from (
------------- Bonus -------------------
select inv_date,vdate,inv_co_code,inv_co_name,inv_fnd_code,inv_fnd_name,inv_nofu,Inv_amount,inv_uprice,'B' Status from investment
where code is null and inv_date <= :dd1
---------------- Redemption --------------
Union All
select red_date,null,red_co_code,red_co_name,red_fnd_code,red_fnd_name,red_nofu,red_amount,red_uprice,'R' Status from redemption
where red_date <= :dd1
))
where co_code = 13 and fnd_code = 1
order by co_code,fnd_code,ddate ;
------------------------------------------
vddate ddate%type;
vrefdate refdate%type;
vco_code co_code%type;
vco_name co_name%type;
vfnd_code fnd_code%type;
vfnd_name fnd_name%type;
vunits units%type;
vamount amount%type;
vstat stat%type;
Vavg number;
tot number;
RUnits number;
BEGIN
OPEN bnr;
LOOP
FETCH bnr INTO vddate,vrefdate,vco_code,vco_name,vfnd_code,vfnd_name,vunits,vamount,vstat;
EXIT WHEN bnr%NOTFOUND;
if vstat = 'R' then
---while :inv_co_code = vco_code and :inv_fnd_code = vfnd_code
:returnreport.redunits := nvl(Vunits,0) + :returnreport.redunits;
end if;
END LOOP;
com;
CLOSE bnr;
END;
|
|
|
|
| Re: Update Table with cursor [message #477973 is a reply to message #477953] |
Tue, 05 October 2010 07:52  |
cookiemonster
Messages: 13938
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Do you really format your code like that? multiple declares on the same line? Indentation all over the place?
I suggest you fix that so it's actually readable.
Then I suggest you explain how your cursor is not working. What is it doing?
And then explain your requirement properly becuase it doesn't really say anything useful:
Quote:
before Update first of all Find inv_co_code & inv_fnd_code
based on what criteria?
Quote:
then update column Redunits
to what exactly?
|
|
|
|
| Re: Update Table with cursor [message #477976 is a reply to message #477973] |
Tue, 05 October 2010 08:09 |
kame
Messages: 69
Registered: July 2009
|
Member |
|
|
There is 2 records in my table. I mentioned Cursor 1 for inserted records so these are the records that I inserted with cursor 1.
select * from returnReport
INV_DATE INV_CO_CODE INV_FND_CODE INV_NOFU BONUS INV_AMOUNT INV_UPRICE REDUNITS REDAMOUNT
----------------------- ----------- ------------ ---------------------- ---------------------- ----------------- ---------------------- ---------------------- ----------------------
28/06/2010 13 1 148375.7308 15104708.75 101.8004
30/08/2010 13 1 100284.0398 10200000.00 101.7111
2 ro
these records I can see on my screen as i mentioned in my 1st cursor.here in above table you can see that REDUNIT column is empty.
after this I created 2nd cursor this cursor query result is
DDATE REFDATE CO_CODE FND_CODE UNITS AMOUNT STAT
----------------------- ----------------------- ---------- ---------- ---------- ---------- ----
01/07/2010 28/06/2010 13 1 2804.7464 B
31/08/2010 13 1 39315.8646 4000000 R
08/09/2010 13 1 24515.9089 2500000 R
28/09/2010 13 1 80000 90000 R
30/09/2010 13 1 11693.4219 1200000 R
5 rows selected
in 2nd cursor I want that in ReturnReport Table record find :inv_co_code = Vco_code and :inv_fnd_code = Vfnd_code
and replace this query units = REDUNIT where STAT ='R'
When I did this the UNIT that stat=R replace on both entry in returnReport and I want that go to first record check the returnreport.inv_nofu = units (of 2nd query) if both are equal then go to next record and if there is any remaining balance then replace it in 2nd row (REDUNITS).
[Updated on: Tue, 05 October 2010 08:14] Report message to a moderator |
|
|
|
| Re: Update Table with cursor [message #477993 is a reply to message #477976] |
Tue, 05 October 2010 09:55 |
cookiemonster
Messages: 13938
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
kame wrote on Tue, 05 October 2010 14:09
in 2nd cursor I want that in ReturnReport Table record find :inv_co_code = Vco_code and :inv_fnd_code = Vfnd_code
Vco_code? you mean co_code? Refer to columns by their names rather than the names of variables that you have bothered to list in your example.
It's less confusing that way.
kame wrote on Tue, 05 October 2010 14:09
and replace this query units = REDUNIT where STAT ='R'
I assume that means you want to set the redunit datablock item with the value of unit from the cursor? For records in the cursor where STAT='R'
kame wrote on Tue, 05 October 2010 14:09
When I did this the UNIT that stat=R replace on both entry in returnReport and I want that go to first record check the returnreport.inv_nofu = units (of 2nd query) if both are equal then go to next record and if there is any remaining balance then replace it in 2nd row (REDUNITS).
First off which record from the cursor do you want to use, because there are three that match your criteria.
If it's more than one then do you want to use the sum of units, min(units), max(units), something else?
Define remaining balance.
Again your rquirement is far from clear.
|
|
|
|
| Re: Update Table with cursor [message #478060 is a reply to message #477993] |
Wed, 06 October 2010 00:38 |
kame
Messages: 69
Registered: July 2009
|
Member |
|
|
for Inserting records created cursor Invest, records are inserted and I can see on my screen (form) also.
Record that entered with Cursor Invest.
select * from returnReport
select * from returnReport
INV_DATE INV_CO_CODE INV_FND_CODE INV_NOFU BONUS INV_AMOUNT INV_UPRICE REDUNITS REDAMOUNT
----------------------- ----------- ------------ ---------------------- ---------------------- ----------------- ---------------------- ---------------------- ----------------------
28/06/2010 13 1 148375.7308 15104708.75 101.8004
30/08/2010 13 1 100284.0398 10200000.00 101.7111
2 row
in above query result you can see that REDUNIT column is null now I want to update Value in this column one by one with loop.
so I created a another cursor.
2nd cursor query result is
DDATE REFDATE CO_CODE FND_CODE UNITS AMOUNT STAT
----------------------- ----------------------- ---------- ---------- ---------- ---------- ----
01/07/2010 28/06/2010 13 1 2804.7464 B
31/08/2010 13 1 39315.8646 4000000 R
08/09/2010 13 1 24515.9089 2500000 R
28/09/2010 13 1 80000 90000 R
30/09/2010 13 1 11693.4219 1200000 R
5 rows selected
I want that when I run Cursor 1 insert record into table returnreport after that when Cursor 2 run first it check returnreport table if there :RETURNREPORT.INV_CO_CODE = CO_CODE and :RETURNREPORT.INV_FND_CODE = FND_CODE then Update UNITS = :REDUNITS one by one.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Two Cursor Variable calculation with each others [message #478115 is a reply to message #478112] |
Wed, 06 October 2010 06:42 |
kame
Messages: 69
Registered: July 2009
|
Member |
|
|
Suppose
Cursor Cursor1 is
select Idate,Icode,IFcode,Iqty,IPrice from returnreport;
Cursor Cursor2 is
select Rdate,Rcode,Rfcode,Rqty,Rprice from TempRed;
Cursor1 result is
IDATE Icode Ifcode Iqty IPRICE REDUNITS
---------- ----- ------ ------------ --------- -----------
28/06/2010 13 1 148375.7308 101.8004
30/08/2010 13 1 100284.0398 101.7111
Cursor2 Result is
Rdate RCODE RFCODE Rqty Rprice
---------- ------ ------- ---------- ---------
01/07/2010 13 1 2804.7464
31/08/2010 13 1 39315.8646
08/09/2010 13 1 24515.9089
28/09/2010 13 1 80000
30/09/2010 13 1 11693.4219
I want to update Cursor 1 table's Column REDUNIT with Cursor 2.
go_block('returnreport');
first_record;
check here IQTY of RETURNREPORT
Loop
Cursor1.Iqty - Cursor2.Rqty
it is like a first in first out basis
check balance = Iqty - Rqty
Update Redunits = Balance
if balance = 0 then
go 2nd row
and update remaining balance in 2ns row.
hope you understand.
[Updated on: Wed, 06 October 2010 06:46] Report message to a moderator |
|
|
|
| Re: Two Cursor Variable calculation with each others [message #478123 is a reply to message #478115] |
Wed, 06 October 2010 07:27 |
cookiemonster
Messages: 13938
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
So as I thought you were asking about the exact same issue. So I've merged your two threads.
Something like this (Pseudo code):
DECLARE
l_current_iqty table.iqty%type;
l_remainder table.iqty%type;
BEGIN
go_block();
first_record;
l_current_iqty := :block.iqty;
FOR rec in CURSOR2 LOOP
l_current_iqty := l_current_iqty - rec.rqty;
IF l_current_iqty < 0 THEN
l_remainder := 0-l_current_iqty;
:block.redunits := 0;
next_record;
l_current_iqty := :block.iqty - l_remainder;
END IF;
END LOOP;
:block.redunits := l_current_iqty;
END;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
