| Cursor update [message #355622] |
Sun, 26 October 2008 10:33  |
shanks009
Messages: 2
Registered: October 2008
|
Junior Member |
|
|
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 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=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. Rite now its taking cursor is taking the last value only and updating the whole.The iteration is not working.
with regards
|
|
|
|
| Re: Cursor update [message #355626 is a reply to message #355622] |
Sun, 26 October 2008 11:41  |
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
|
Senior Member |
|
|
| Quote: |
I need a urgent help on the pl/sql written below
|
Since this request this deemed as urgent I assume this would have sorted by this time.
Good luck
Regards
Raj
|
|
|
|
| Re: Cursor update [message #355629 is a reply to message #355626] |
Sun, 26 October 2008 11:55 |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
If cursor selects everything but YEN and EUR, what is the purpose of the IF-THEN-ELSE construct? "b.treccy" will never be YEN nor EUR.
| Quote: | its taking cursor is taking the last value only and updating the whole
|
Hm? The last value only? What is "the last value"? What is "the whole"?
Besides, if it is not a problem, would you, please, read the OraFAQ Forum Guide before posting your next message? It might help you write a better post (easier to understand, using [code] tags to preserve code formatting).
P.S. Forgot to mention: the code you have posted is NOT the one you use. It is full of syntax errors (single quotes not closed, semicolons at the end of a statement, END IF is missing, ...). If you expect any help, well, you won't get it unless you provide accurate code.
[Updated on: Sun, 26 October 2008 12:01] Report message to a moderator |
|
|
|
| Re: Cursor update [message #355631 is a reply to message #355629] |
Sun, 26 October 2008 12:23 |
shanks009
Messages: 2
Registered: October 2008
|
Junior Member |
|
|
Hi,
Sorry for that.
Actually i have written the code now only and not copied from the toad.Sorry again
The last value i mean is that i am taking about the cursor dataset.
Let say the last dataset is treccy='AUD' in cursor and TREAMT=12
select fxrate into rate from fxrate where fxfrccy='EUR'-->rate =154.95
select fxrate into rate2 from fxrate where fxrate not in ('YEN,'EUR') and fxfrccy =b.treccy;-->rate2=123 where fxrate.fxfrccy=treccy of tredtl
Now update statement will update eur_equivalent_amount when TRECCY<>'YEN' and TRECCY<>'EUR' and treccy=b.treccy refering to the cursor dataset.
After it exists the loop.
I dont know where i am getting wrong coz data problem is not there.
can u suggest any other way around if possible?
waiting for reply
regards
shanks
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;
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*rate2/rate. Rite now its taking cursor is taking the last value only and updating the whole.The iteration is not working.
with regards
|
|
|
|
|
|
| Re: Cursor update [message #355643 is a reply to message #355631] |
Sun, 26 October 2008 15:56 |
|
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
This is absurd.
I have asked you to post exact code you use, but you have posted an invalid PL/SQL block once again. Copying exactly the same "explanation" doesn't help at all.
So, if you refuse to provide relevant information, why would I bother answering your question?
Besides, it would not surprise me if there's the WHEN OTHERS exception handler in there.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
