I am trying to join an exchange rate table in my code to the order lines table so I can convert the currency to USD in all my reports. The issue is for order lines that are already in USD, on the exchange rates table there is no conversion from USD to USD so my join isn't bringing in all the order lines. How can I make this join work so that anytime there is an order line in USD currency, it doesn't try to find a match in the exchange rates table but still shows the order lines in USD currency in my output?
select ol.account_name ,a."segment-2" as Cost_Center ,dgl."name" ,bm."budget owner" ,oh.requested_by as PO_Requester ,oh."po-number" ,ol."line-num" as PO_Line ,oh.requisition_header_id ,cast(rh."submitted-at" as Date) as req_submitted_date ,cast(oh."created-at" as Date) as order_date ,cast(ol.start_date as Date) ,cast(ol.end_date as Date) ,ol.supplier_name ,oh.status ,ol.description as Item_Description ,ol.quantity ,ol.total as Line_Total ,ol.currency_code ,ol.received as Received_Amt_Qty ,ol.invoiced as Invoiced_Amt_Qty ,er.rate as Exchange_Rate ,round(case when oh.currency_code = 'USD' then ol.total::FLOAT else ol.total::FLOAT * er.rate::FLOAT end,2) as Converted_Total ,ol."savings-pct" ,round(case when ol.currency_code = 'USD' then ol.total::FLOAT / ('1' - (ol."savings-pct"::FLOAT / '100')) * (ol."savings-pct"::FLOAT / '100') else (ol.total::Float * er.rate::Float) / ('1' - (ol."savings-pct"::FLOAT / '100')) * ol."savings-pct"::FLOAT / '100' end, 0) as Savings_Total FROM stg_coupa.order_lines AS ol join stg_coupa.accounts AS a on ol.account_name = a."name" join stg_coupa.budget_mapping as bm on bm."cost center #" = a."segment-2" join stg_coupa.purchase_orders as oh on ol.po_id = oh.id join stg_coupa.requisitions as rh on rh.id = oh.requisition_header_id join stg_coupa.exchange_rates er on cast(ol."created-at" as Date) + ol.currency_code + 'USD' = cast(er."rate-date" as Date) + er.from_currency + er.to_currency join stg_coupa.dept_gl_lookups dgl on bm."cost center #" = dgl."external ref num"