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

change your view like this:

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( 
        IF(IP.CURRENCY = 'USD_OLD' , 'USD', IP.CURRENCY)  = FX.CURRENCY
      )