3/23/2023 0 Comments Xbox live currency converterLEFT JOIN rates r1 ON r1.from_currency = t.currency AND r1.to_currency = params.target_currency Params.target_currency converted_currency WHEN r1.from_currency IS NOT NULL THEN r1.exchange_rateĮLSE r2usd.exchange_rate / r2tar.exchange_rate END converted_value, T.transaction_value * CASE WHEN t.currency = params.target_currency THEN 1 SELECT 'txn 4 IN unknown', 1000, 'XXX' FROM DUAL), SELECT 'txn 3 IN CNY', 1000, 'CNY' FROM DUAL UNION ALL SELECT 'txn 2 in GBP', 1000, 'GBP' FROM DUAL UNION ALL ( SELECT 'txn 1 in JPY', 1000, 'JPY' FROM DUAL UNION ALL Txns ( transaction_name, transaction_value, currency ) AS SELECT 'CNY', 'USD', 0.15 FROM DUAL UNION ALL SELECT 'GBP', 'USD', 1.31 FROM DUAL UNION ALL ( SELECT 'JPY', 'USD', 0.009 FROM DUAL UNION ALL WITH rates ( from_currency, to_currency, exchange_rate ) AS The WITH clauses are just to give it some data - they won't be part of your solution, since you have the actual tables. The transaction must be converted to USD and then from USD to the target currency.The transaction is in a currency that is directly convertible to the target currency.The transaction is already in the target currency.Given that, I would just code for all the possible cases rather than try something fancy like a CONNECT BY. At most, you'll convert to USD and then from USD to whatever. If your EXCHANGE_RATE table is exhaustive to USD, then you won't ever have more than two "hops" to do your conversion. LEFT JOIN EXCHANGE_RATE C ON S.CURRENCY = C.FROM_CURRENCY AND C.TO_CURRENCY = 'GBP' I built this query first to convert to USD, which works fine (as my Exchange Rate table contains values for all currencies to USD) - but it obviously fails when setting the destination currency as GBP, as it'll just return nulls. The query (very basic) I've built so far is as follows, and I'm a novice SQL user.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |