0

Please find the below sample data. There are 2 tables Transaction and Exchange rate. If i need to convert the transactions table to USD then below is the query which i have tried but it doesnt give the required output.

Transaction

Currency Local_Price
USD 1000
GBP 100
EUR 10
USD 100

ExchangeRate

From_Currency To_Currency Exchange_Rate
USD GBP 0.9
EUR USD 1.1
GBP USD 1.3

Expected Output

Currency Local_Price In_USD
USD 1000 1000
GBP 100 130
EUR 10 11
USD 100 100

Query Select Currency,Local_price, Local_Price*Exchange_Rate as In_USD from Transaction T left join ExchangeRate ER on T.Currency=ER.From_Currency and To_Currency='USD'

I do not have data from USD to USD or EUR to EUR or GBP to GBP in my exchange rate table.

Please help me with the query for required output.

user2293950
  • 39
  • 6

1 Answers1

1

Use coalesce():

Select Currency,Local_price,
       ( Local_Price * coalesce(Exchange_Rate, 1) ) as In_USD
from Transaction T left join
     ExchangeRate ER
     on T.Currency = ER.From_Currency and To_Currency = 'USD';
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • How do i get all three currency conversions in one table. Similar to USD lets say GBP and EUR. GBP, EUR and USD in one table against local currency – user2293950 Apr 29 '21 at 18:40
  • @user2293950 . . . This answers the question you have asked here. If you have a different question, ask it as a *new* question, with sample data, desired results, and a clear explanation. – Gordon Linoff Apr 30 '21 at 00:53
  • Added new question. https://stackoverflow.com/questions/67328154/multiple-currency-conversions-in-single-query – user2293950 Apr 30 '21 at 04:28