Cursor update

[shanks0092001]

Hi All,
I need a urgent help on the pl/sql written below

declare
rate integer;
cursor c_f is
select treccy,treamt from tredtl where treccy not in ('YEN,'EUR');
begin
select fxrate into rate where fxfrccy='EUR'
for b in c_f
loop
select fxrate into rate2 where fxrate not in ('YEN,'EUR') and fxfrccy =b.treccy;
if b.treccy<>'YEN' and b.treccy<>'EUR' then
update tredtl
set eur=b.treamt*ra te/rate2
where treccy=b.treccy
end loop;
end;

Please anyone let me know where i am going wrong
here my requirement is that i need to update a column in tredtl table.
first i am fetching all the data set of tredtl into cursor where treccy is not equal to eur and yen.
In rate1 variable i am taking a value from fxrate table which is having fxfrccy=EUR
Inside for loop i am fetching in the select statement fxrate into rate2 one by one where fxfrccy =b.treccy.
say if am fetching from the cursor treccy=USD and rate1=154.95
and rate2=98 where fxfrccy=b.trecc y=USD
Now update eur=b.treamt*ra te/rate2.
Please help me

[debasisdas]

TRY THE FOLLOWING CODE

declare
rate integer;
cursor c_f is
select treccy,treamt from tredtl where treccy not in ('YEN','EUR');
begin
select fxrate into rate from TABLE_NAME where fxfrccy='EUR' 
for b in c_f
loop
select fxrate into rate2 from TABLE_NAME where fxrate not in ('YEN,'EUR') and fxfrccy =b.treccy;
if b.treccy<>'YEN' and b.treccy<>'EUR' then
update tredtl
set eur=b.treamt*rate/rate2
where treccy=b.treccy
end loop;
end;

TABLE_NAME---Add your tablename here.

[shanks0092001]

[QUOTE=debasisda s]TRY THE FOLLOWING CODE

declare
rate integer;
cursor c_f is
select treccy,treamt from tredtl where treccy not in ('YEN','EUR');
begin
select fxrate into rate from TABLE_NAME where fxfrccy='EUR' 
for b in c_f
loop
select fxrate into rate2 from TABLE_NAME where fxrate not in ('YEN,'EUR') and fxfrccy =b.treccy;
if b.treccy<>'YEN' and b.treccy<>'EUR' then
update tredtl
set eur=b.treamt*rate/rate2
where treccy=b.treccy
end loop;
end;

TABLE_NAME---Add your tablename here.[/I have added the TABLE NAME in the real code but i missed out while writing here.
Can u tell me the problem in this code coz i m only getting the last value from the cursor and its updating the column with same value.]

[debasisdas]

Please post your exact code that you are working on.

Where is the END IF; part in your code.

[shanks0092001]

Please post your exact code that you are working on.

Where is the END IF; part in your code.

declare
rate integer;
cursor c_f is
select treccy,treamt from tredtl where treccy not in ('YEN','EUR');
begin
select fxrate into rate from fxrate where fxfrccy='EUR'
for b in c_f
loop
select fxrate into rate2 from fxrate where fxrate not in ('YEN,'EUR') and fxfrccy =b.treccy;
if b.treccy<>'YEN' and b.treccy<>'EUR' then
update tredtl
set eur_equivalent_ amount=b.treamt *rate2/rate
where treccy=b.treccy
end if;
end loop;
end;

My only prob in this code is that its not iterating.
I dont know why its taking only the last value from the cursor and updating all.
Should use fetch instead of for loop.
But fetch making it very slow.

[debasisdas]

how many records your cursor returns ?

and howmany by the following query

select fxrate into rate from fxrate where fxfrccy='EUR'