December 17, 2015 at 10:19 am
I am trying to create a script for a price increase. The current price displays as 4 decimals (20.7340) in our ERP software. I'd like to use this script:
Update AB_Price_Increase SET UNIT_PRICE_1 = (UNIT_PRICE_1 * 1.028) to display 21.310 but instead it displays 21.314552
The field is a decimal 15,6
Can I round this number to be only 4 decimal places?
Thank you in advance for your help.
December 17, 2015 at 10:31 am
First, welcome to SQL math. 😀
Second - have a read on the round function and the arguments available.
Third - have a look at data type precedence because this is going to come back to bite you more times than you think. At least it does for me.
Finally - think about casting constants within the expression so you're working with the exact numbers you want.
UPDATE AB_Price_Increase
SET UNIT_PRICE_1 = (UNIT_PRICE_1 * CAST(1.028 AS DECIMAL(15, 6)));
I'm of the mindset it's best to be up front about what's going on with my expressions even at the cost of a little performance. I've had issues with rounding errors at the end of multiple step math functions that were resolved with casting data types in this method.
December 17, 2015 at 11:37 am
Thank you very much for the explanation, I think using CAST will work. I have some more testing to do but it looks good so far. I really appreciate the help!
December 18, 2015 at 10:03 am
Glad that I could help.
Another consideration would be to create desired data type variables for your constants and set them at the start of the process. Then you're only casting the number once instead of every time it reads a row. But that's a 'next step' in tuning what you're doing and possibly not applicable for this instance.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply