443,705 Members | 2,017 Online
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
5 Replies

 Expert 5K+ P: 8,127 TRY THE FOLLOWING CODE Expand|Select|Wrap|Line Numbers 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. Oct 26 '08 #2

 P: 3 [quote=debasisdas]TRY THE FOLLOWING CODE Expand|Select|Wrap|Line Numbers 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.] Oct 26 '08 #3

 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

 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