473,320 Members | 1,713 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Cursor update

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 3018
debasisdas
8,127 Expert 4TB
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
[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
8,127 Expert 4TB
Please post your exact code that you are working on.

Where is the END IF; part in your code.
Oct 26 '08 #4
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
8,127 Expert 4TB
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

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

Similar topics

4
by: carol | last post by:
I need to do something relatively simple… I need to update a table using a cursor. (I may have to create a stored procedure for doing this…) I need to declare an update cursor, fetch the...
2
by: php newbie | last post by:
I am trying to write a cursor to update certain rows in a particular order as follows: (I need the cursor version, not SQL, as the update logic depends on the order of rows and some other...
7
by: Philip Mette | last post by:
Does anyone have any good references they could recommend on Cursor based SQL writing? I have to create SQL that can loop though records simular to VB loops and I have been told that this is the...
15
by: Philip Mette | last post by:
I am begginner at best so I hope someone that is better can help. I have a stored procedure that updates a view that I wrote using 2 cursors.(Kind of a Inner Loop) I wrote it this way Because I...
1
by: teddysnips | last post by:
SQL Server 2000 I have a stored procedure that uses an extended SPROC to send an email notification to my customers when a document is distributed. However, the SPROC has an unexpected side...
5
by: Paul M | last post by:
Hi All, I've been tasked with writing a z/OS C program to read and update a table (DB/2 v8). This Table contains a single row with a single column of data. This program (which will run as a...
3
by: Mark A | last post by:
In the DB2 Admin Client (8.2.4), when the results of a query are displayed in the grid that looks like a spreadsheet (using Command Editor or double click on table name in Control Center). The data...
0
debasisdas
by: debasisdas | last post by:
Sample example to show FOR UPDATE CURSOR ----------------------------------------------------------------------------- DECLARE CURSOR EMPREC IS SELECT * FROM EMP FOR UPDATE OF SAL; MYREC...
9
by: Frank Swarbrick | last post by:
New to SQL here... We have a CURSOR declared thusly: EXEC SQL DECLARE ALL-ADJSTMTS-CSR CURSOR FOR SELECT ACCT.ACCOUNT_ID , ACCT.APPL_ID , ACCT.BRANCH_NUMBER , ACCT.CATEGORY_CODE
1
by: vituko | last post by:
plpgsql (postgresql 8.3 but I can upgrade) I can open a cursor with a dynamic query (table / column variable) : -open cursor for execute '...' ; But if I want do updates... - execute 'update...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.