I am currently trying to set a rate which varies according to the type of currency, for example: If the currency is USD then the rate will be 1,000 (4 decimal places after the point) If the currency is HNL, a formula is made and the result has me to give 2 decimals before the point and 4 after the point (Example: 24.1141)
This is the condition I am using
(CASE
WHEN bp.Currency = 'USD' THEN '1.0000'
ELSE
(SELECT TOP 1
er.BuyCash
FROM dbo.ExchangeRates AS er
WHERE CONVERT(DATE, er.ValidDate) = CONVERT(DATE, bp.ValueDate)
AND er.BranchId = 0)
END)
However, when executing everything, when the currency is USD, it gives me a rate of 1.00000, putting many zeros
BuyCash
that is surely defined asNUMERIC(13,6)
or a similar value. Finally, the engine chooses the most "inclusive" data type, in this case the one with the highest number of decimals.The solution is to cast the value to a type that has the desired decimal places, but remember that this would eventually be truncating data.