SQLTeam.com | Weblogs | Forums

Joining tables where one table only has partial data

Hey all,

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"

Change "join" to "left outer join" for the table that holds the exchange rates.

Thanks for the info Scott. Seems like I'm having an issue where I have null values in my case statement so I'm not seeing all the results to verify if that worked. Going to go try and figure out the null if statement for my two case statements.