May 19 2022 04:39 AM
DECLARE @ConversionCoeff MONEY = 1
DECLARE @IngQtyLackInGoods MONEY
DECLARE @IngQtyLack MONEY = 0.4190
DECLARE @GoodsCountable BIT = 0
-- return incorrect
SET @IngQtyLackInGoods =
CASE WHEN @GoodsCountable = 1
THEN
CEILING(CEILING((@IngQtyLack * 10000.00) / @ConversionCoeff) / 10000.00)
ELSE
CEILING((@IngQtyLack * 10000.00) / @ConversionCoeff) / 10000.00
END
SELECT @IngQtyLackInGoods incorrect
--return correct
IF @GoodsCountable = 1 BEGIN
SET @IngQtyLackInGoods = CEILING(CEILING((@IngQtyLack * 10000.00) / @ConversionCoeff) / 10000.00)
END
ELSE BEGIN
SET @IngQtyLackInGoods = CEILING((@IngQtyLack * 10000.00) / @ConversionCoeff) / 10000.00
END
SELECT @IngQtyLackInGoods correct
May 19 2022 05:55 PM
Hi @SanyaSan -- I think the difference between the two is the result of data type precedence within a CASE statement. They "incorrect" query uses a CASE statement and according to data type precedence rules, numeric/decimal is higher than money and will therefore produce a numeric data type value.
The "correct" query of course has no CASE statement and is explicitly setting the @IngQtyLackInGoods variable which has the money datatype. Therefore, this query is not impacted by data type precedence. Take care.
May 19 2022 10:53 PM
Thanks man
But I didn't understand why there is no error in the second piece of code (IF...)
Also, in the first piece of code, if you fix the first branch of CASE (CEILING) calculations, for example, put 10000 instead of 10000.00, the second branch begins to count correctly, why does it even climb to calculate CEILING (CEILING, despite the fact that @GoodsCountable = 0 ?
May 26 2022 11:13 PM