By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,705 Members | 2,017 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,705 IT Pros & Developers. It's quick & easy.

Cursor update

P: 3
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*rate/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.treccy=USD
Now update eur=b.treamt*rate/rate2.
Please help me
Oct 24 '08 #1
Share this Question
Share on Google+
5 Replies


debasisdas
Expert 5K+
P: 8,127
TRY THE FOLLOWING CODE

Expand|Select|Wrap|Line Numbers
  1. declare
  2. rate integer;
  3. cursor c_f is
  4. select treccy,treamt from tredtl where treccy not in ('YEN','EUR');
  5. begin
  6. select fxrate into rate from TABLE_NAME where fxfrccy='EUR' 
  7. for b in c_f
  8. loop
  9. select fxrate into rate2 from TABLE_NAME where fxrate not in ('YEN,'EUR') and fxfrccy =b.treccy;
  10. if b.treccy<>'YEN' and b.treccy<>'EUR' then
  11. update tredtl
  12. set eur=b.treamt*rate/rate2
  13. where treccy=b.treccy
  14. end loop;
  15. end;
TABLE_NAME---Add your tablename here.
Oct 26 '08 #2

P: 3
[quote=debasisdas]TRY THE FOLLOWING CODE

Expand|Select|Wrap|Line Numbers
  1. declare
  2. rate integer;
  3. cursor c_f is
  4. select treccy,treamt from tredtl where treccy not in ('YEN','EUR');
  5. begin
  6. select fxrate into rate from TABLE_NAME where fxfrccy='EUR' 
  7. for b in c_f
  8. loop
  9. select fxrate into rate2 from TABLE_NAME where fxrate not in ('YEN,'EUR') and fxfrccy =b.treccy;
  10. if b.treccy<>'YEN' and b.treccy<>'EUR' then
  11. update tredtl
  12. set eur=b.treamt*rate/rate2
  13. where treccy=b.treccy
  14. end loop;
  15. 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.]
Oct 26 '08 #3

debasisdas
Expert 5K+
P: 8,127
Please post your exact code that you are working on.

Where is the END IF; part in your code.
Oct 26 '08 #4

P: 3
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.
Oct 26 '08 #5

debasisdas
Expert 5K+
P: 8,127
how many records your cursor returns ?

and howmany by the following query

select fxrate into rate from fxrate where fxfrccy='EUR'
Oct 27 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.