Conditional Select in View - MYSQL

Question

I have a view that needs to show the converted price of items. I have 2 tables to join from Item_Price & FX_Rate

Simplified example:

Item table with millions of data

IP.ITEM	Item_Price.PRICE	Item_Price.CURRENCY
Apple	1	USD
Orange	77	INR
Avocado	22	MXN
Pear	0.9	USD_OLD

FX Rate table which is consistently updated with real-time rate

FX_Rate.CURRENCY	FX_Rate.FX
USD	1
INR	0.013
MXN	0.049
USD_OLD	1.79

Both table structures can't be modified, so I'm creating a view to display the standardized price of items with :

CREATE VIEW USD_ITEMS AS
  SELECT 
    IP.NAME AS NAME,
    (Item_Price.PRICE * FX_Rate.FX) AS USD_price
    ...
    FROM 
      Item_Price IP
      JOIN FX_Rate FX ON( 
        IP.CURRENCY = FX.CURRENCY
      )

While everything shall follow, USD_OLD is now outdated and all items with USD_OLD as currency need to be converted with USD's fx rate instead

I tried to use IF / CASE WHEN but I can't find a way to purposely switch the FX where CURRENCY='USD_OLD'

Error Pseudocode? :

CASE USD_price 
  WHEN FX_Rate.CURRENCY='USD_OLD' 
    THEN (Item_Price.PRICE * FX_Rate.FX WHERE FX_Rate.CURRENCY='USD_OLD')   
  ELSE (Item_Price.PRICE * FX_Rate.FX)

I know the above is not possible as the view is JOIN ON Item_Price.CURRENCY = FX_Rate.CURRENCY

Will a temporary table in the view help?

I can't work my head around this and really need some expert help, thank you.

Answer 1

You'll need to join the USD row all the time and then use it only when necessary. For example:

create view usd_items as
select
  ip.name,
  ip.price * case when ip.currency <> 'USD_OLD' then fx.fx else u.fx end
  as usd_price
from item_price ip
join fx_rate fx on ip.currency = fx.currency
cross join (
  select * from fx_rate where currency = 'USD'
) u