Adding calculation to case when operator

Transact-SQL
1

Hello,

I am trying to convert all spend on a report from different currencies to USD so all spend is in the same currency for my report. I was able to get the exchange rates into my table but now when I create the case when operator I am getting an error when I add the calculation to multiply the line total to the exchange rate. Here is my code so far. Any suggestions on how I can calculate the exchange rate within my case when operator so it doesn't throw an error?

ol = order line
er = exchange rate

,case when ol.currency_code = 'USD' then ol.total * '1' else ol.total * er.rate end Converted_Total
2

You didn't show much, not even the error. Is it objecting to multiplying by '1', which would be a character instead of numeric quantity?

3

Hi Fred -

Thanks for the reply. Here is what I used for the calc and I managed to get this to line up with what I expected to see:

,round(case when oh.currency_code = 'USD' then ol.total::FLOAT else ol.total::FLOAT * er.rate::FLOAT end,2) as Converted_Total