Implicitly Calculate Exchange Rates in SQL from Exchange Rate table

Question

I have one table of data with revenue in multiple currencies (let's call this one TRANSACTION_TABLE, with columns as such:

TRANSACTION_NAME
TRANSACTION_VALUE
CURRENCY

, and another table with exchange rates (EXCHANGE_RATE) with columns as such:

FROM_CURRENCY (e.g. JPY)
TO_CURRENCY (e.g. USD)
EXCHANGE_RATE (x)

The table has, at minimum, every currency converting to USD, but is not exhaustive with exchange rates for non-USD TO_CURRENCY values.

What I'm trying to achieve, is a query which converts the transactions to any currency, even if not explicitly stipulated in the EXCHANGE_RATE table, by converting the currencies to USD first, and then from USD into the destination currency.

E.g. 1000 JPY to GBP:

1. Find rate JPY to USD - calculation = 1000 * EXCHANGE_RATE = 9
2. Find rate GBP to USD - calculation = 9 \ EXCHANGE_RATE = 7

At the moment, I've done a left join for TRANSACTION_TABLE on EXCHANGE_RATE but I'm lost at where to go next.

Any assistance would be greatly appreciated.

The query (very basic) I've built so far is as follows, and I'm a novice SQL user. 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.

SELECT TRANSACTION_NAME,
             SUM (TRANSACTION_VALUE * EXCHANGE_RATE)
                 AS "REVENUE GBP"
        FROM TRANSACTION_TABLE S
             LEFT JOIN EXCHANGE_RATE C ON S.CURRENCY = C.FROM_CURRENCY AND C.TO_CURRENCY = 'GBP'
ORDER BY TRANSACTION_NAME

Answer 1

I'd propose to make an extra step to expand you exchange table with the exchange rates additionaly defined using UDS as transfer currency.

This query adds the new rates calulated via USD. It is a simple inner join constrained so that the calculation is via 'USD' and the from and to currencies are different. The WHERE clause limits the already know combinations.

select  er1.FROM_CURRENCY, er2.TO_CURRENCY,  er1.EXCHANGE_RATE * er2.EXCHANGE_RATE EXCHANGE_RATE
from exchange_rates er1
join exchange_rates er2
on er1.TO_CURRENCY = 'USD' and er2.FROM_CURRENCY = 'USD'  and er1.FROM_CURRENCY != er2.TO_CURRENCY
where (er1.FROM_CURRENCY, er2.TO_CURRENCY)
not in (select FROM_CURRENCY,   TO_CURRENCY from exchange_rates)

You may define a physical new table or view or even perform it only as a subquery as an UNION ALL of your original table and the result of this query.

Your final query uses this extended exchange rate table instead of the original one.

Here are sample data I tested with

create table exchange_rates
as
select 'GBP' FROM_CURRENCY,  'USD' TO_CURRENCY, 1.31  EXCHANGE_RATE from dual union all
select 'EUR' FROM_CURRENCY,  'USD' TO_CURRENCY, 1.16 EXCHANGE_RATE from dual union all
select 'AUD' FROM_CURRENCY,  'USD' TO_CURRENCY, .73 EXCHANGE_RATE from dual union all
select 'USD' FROM_CURRENCY,  'GBP' TO_CURRENCY, .76  EXCHANGE_RATE from dual union all
select 'USD' FROM_CURRENCY,  'EUR' TO_CURRENCY, .86 EXCHANGE_RATE from dual union all
select 'USD' FROM_CURRENCY,  'AUD' TO_CURRENCY, 1.36 EXCHANGE_RATE from dual union all
select 'GBP' FROM_CURRENCY,  'EUR' TO_CURRENCY, 1.12 EXCHANGE_RATE from dual;

Answer 2

If your EXCHANGE_RATE table is exhaustive to USD, then you won't ever have more than two "hops" to do your conversion. At most, you'll convert to USD and then from USD to whatever. Given that, I would just code for all the possible cases rather than try something fancy like a CONNECT BY.

"All possible cases", I think, are:

• The transaction is already in the target currency
• The transaction is in a currency that is directly convertible to the target currency
• The transaction must be converted to USD and then from USD to the target currency.

Here is a query that will do that. The WITH clauses are just to give it some data -- they won't be part of your solution, since you have the actual tables.

WITH rates ( from_currency, to_currency, exchange_rate ) AS 
( SELECT 'JPY', 'USD', 0.009 FROM DUAL UNION ALL
  SELECT 'GBP', 'USD', 1.31 FROM DUAL UNION ALL
  SELECT 'CNY', 'USD', 0.15 FROM DUAL UNION ALL
  SELECT 'JPY', 'CNY', 0.06 FROM DUAL),
txns ( transaction_name, transaction_value, currency ) AS
 ( SELECT 'txn 1 in JPY', 1000, 'JPY' FROM DUAL UNION ALL
   SELECT 'txn 2 in GBP', 1000, 'GBP' FROM DUAL UNION ALL
   SELECT 'txn 3 IN CNY', 1000, 'CNY' FROM DUAL UNION ALL
   SELECT 'txn 4 IN unknown', 1000, 'XXX' FROM DUAL),
params ( target_currency ) AS 
 ( SELECT 'CNY' FROM DUAL )
SELECT t.transaction_name,
       t.transaction_value base_value,
       t.currency base_currency,
       t.transaction_value * CASE WHEN t.currency = params.target_currency THEN 1
            WHEN r1.from_currency IS NOT NULL THEN r1.exchange_rate
            ELSE r2usd.exchange_rate / r2tar.exchange_rate END converted_value,
        params.target_currency converted_currency
FROM   params CROSS JOIN 
       txns t 
       LEFT JOIN rates r1 ON r1.from_currency = t.currency AND r1.to_currency = params.target_currency
       LEFT JOIN rates r2usd ON r2usd.from_currency = t.currency AND r2usd.to_currency = 'USD'
       LEFT JOIN rates r2tar ON r2tar.from_currency = params.target_currency AND r2tar.to_currency = 'USD'