Euro Conversion

[joeythelips]

Hello.

I have an Oracle database.
One table has 2 fields with currency values.
I want to update these fields (TotalCash1) and (TotalCash2) to euro currency. The conversion rate is to multiply the old figure by 1.27.
There is also another field in this table with Currency type. I want to Updates this from IRP TO EURO.

If someone had the code for this, it would be much appreciated.

Joe

 

[THoey]

Feeling rusty this morning, but does this work?

UPDATE MyTable (MyTable.TotalCash1,
MyTable.TotalCash1,
MyTable.CurrencyType)
VALUES ((MyTable.TotalCash1 * 1.27),
(MyTable.TotalCash1 * 1.27),
('EURO'))
WHERE MyTable.CurrencyType = 'IRP'; Terry M. Hoey

 

[SpiritOfLennon]

SQL aside are you aware of the actual legal rules governing Euro conversions? As you seem to be in Irish Punts you have to follow the correct procedures which certainly involve a lot more than two decimal places. I would be happy to provide the guidelines if required. SOL
I'm only guessing but my guess work generally works for me.

 

[joeythelips]

Any guidelines you could provide would be much appreciated.

Joe

 

[sem]

You may insert records using VALUES clause or update them using =

UPDATE MyTable set
MyTable.TotalCash1 = MyTable.TotalCash1 * 1.27,
MyTable.TotalCash1 = MyTable.TotalCash1 * 1.27,
MyTable.CurrencyType ='EURO'
WHERE MyTable.CurrencyType = 'IRP';

OR insert new 'converted' records

insert into MyTable T (
TotalCash1,
TotalCash1,
CurrencyType)
select
M.TotalCash1 * 1.27,
M.TotalCash1 * 1.27,
'EURO'
from MyTable M
WHERE M.CurrencyType = 'IRP';

 

[SpiritOfLennon]

The regulations are laid down in article 235 which should turn up in search engines although here are the main pointers

‘The conversion rates have been adopted as one euro, expressed in terms of each of the national currencies of the participating Member States. They shall be adopted with six significant figures.

‘The conversion rates shall not be rounded or truncated when making conversions.’

‘The conversion rates shall be used for conversions either way between the euro unit and the national currency units. Inverse rates derived from the conversion rates shall not be used.’

‘Monetary amounts to be converted from one national currency unit into another shall first be converted into a monetary amount expressed in the euro unit, which amount may be rounded to not less than three decimals and shall be converted into the other national currency unit. No alternative method of calculation may be used unless it produces the same results.’

‘Monetary amounts to be paid or accounted for when a rounding takes place after a conversion into the euro unit pursuant to Article 4 shall be rounded up or down to the nearest cent. Monetary amounts to be paid or accounted for, which are converted into a national currency unit, shall be rounded up or down to the nearest sub-unit or in the absence of a sub-unit to the nearest unit, or according to national law or practice to a multiple or fraction of the sub-unit or unit of the national currency unit. If the application of the conversion rate gives a result, which is exactly half-way, the sum shall be rounded up.’ (Paragraph Article 5 of Article 235.)
SOL
I'm only guessing but my guess work generally works for me.