Click here to Skip to main content
15,883,709 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
i have 2 tables one for all currency and one for order i create a new column called total order in Euro so all the total orders will be in Euro

What I have tried:

-
this is the tables:
<pre>ORDER_ID	CUSTOMER_ID	COUNTRY	ORDER_TOTAL	ORDER_TOTAL_EURO
2435	144	AMERICA	62303	 - 
2363	144	AMERICA	10082.3	 - 
2382	144	AMERICA	71173	 - 
2422	144	AMERICA	11188.5	 - 
2445	144	AMERICA	5537.8	 - 
2455	145	AMERICA	14087.5	 - 
2364	145	AMERICA	9500	 - 
2383	145	AMERICA	36374.7	 - 
2423	145	AMERICA	10367.7	 - 
2448	145	AMERICA	1388	 - 
2379	146	AMERICA	17848.2	 - 
2365	146	AMERICA	27455.3	 - 
2384	146	AMERICA	29249.1	 - 
2424	146	AMERICA	13824	 - 
2449	146	AMERICA	86	 - 
2396	147	AMERICA	34930	 - 
2366	147	AMERICA	37319.4	 - 
2385	147	AMERICA	295892	 - 
2425	147	AMERICA	1500.8	 - 
2450	147	AMERICA	1636	 - 
2406	148	AMERICA	2854.2	 - 
2367	148	AMERICA	144054.8	 - 
2386	148	AMERICA	21116.9	 - 
2426	148	AMERICA	7200	 - 
2451	148	AMERICA	10474.6	 - 
2434	149	AMERICA	268651.8	 - 
2368	149	AMERICA	60065	 - 
2387	149	AMERICA	52758.9	 - 
2427	149	AMERICA	9055	 - 
2452	149	AMERICA	12589	 - 
2388	150	AMERICA	282694.3	 - 
2389	151	AMERICA	17620	 - 
2390	152	AMERICA	7616.8	 - 
2391	153	AMERICA	48070.6	 - 
2392	154	AMERICA	26632	 - 
2393	155	AMERICA	23431.9	 - 
2395	156	AMERICA	68501	 - 
2398	157	AMERICA	7110.3	 - 
2399	158	AMERICA	25270.3	 - 
2400	159	AMERICA	69286.4	 - 
2401	160	AMERICA	969.2	 - 
2402	161	AMERICA	600	 - 
2403	162	AMERICA	220	 - 
2405	164	AMERICA	1233	 - 
2407	165	AMERICA	2519	 - 
2408	166	AMERICA	309	 - 
2409	167	AMERICA	48	 - 
2411	169	AMERICA	15760.5	 - 
2412	170	AMERICA	66816	 - 
2458	101	AMERICA	78279.6	 - 

------------
CUR_SIGN	COUNTRY	CUR_QTY	EURO_RATE
CNY	CHINA	1	.13
INR	INDA	1	.011
CHF	SWITZERLAN	1	.92
THB	THAILAND	1	.027
JPY	JAPAN	1	.0079
EUR	ITALY	1	1
USD	AMERICA	1	.81
EUR	GERMANY	1	1
Posted
Comments
Richard Deeming 24-Oct-22 8:56am    
And? You seem to have forgotten to ask a question.
Member 15807886 24-Oct-22 9:30am    
that's right!
any way my question is how I can insert the value in total_order_Eurou after transforming the currency
Richard MacCutchan 24-Oct-22 9:37am    
You should not be storing the exchange rate, or multiple currencies, in the database. Exchange rates vary from day to day so you should get then from your national bank at the time you need to do the conversion. Similarly you should store all prices in your local currency.
Paul Maxwell 2023 8-Mar-23 21:06pm    
Well, there are many enterprise level systems that rely on storing exchange rates in a database. So the basic concept of storing them in your db is OK IMHO. What I would suggest is that they change regularly, so you need some other columns e.g. "as at" (date/timestamp) and/or timestamp_from so that when you match to transaction you can get the most appropriate rate for when the conversion takes place. Some organisations might use "average rates" or "planning rates" so the frequency of changing the rates is reduced (from "spot rates") but even then you need a period of validity. Then on the transaction side of the conversion, you also need a timestamp for when the transaction happened - which I don't see in your sample data either - perhaps it is available at the "order level" instead of the table you have shown. AND there is no indication which currency has been used when storing the transaction.
Richard MacCutchan 9-Mar-23 3:12am    
Given that exchange rates change quite rapidly these days, I doubt any serious business would store the numbers in a database.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900