Forum Discussion
what is the reason?
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
bake13Microsoft
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.
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 ?
- The problem is that you have different data types in each branch of the CASE statement. In the "incorrect" query, the first option in the CASE statement is returning an int. The return value of the CASE is then an int. So when the second branch is taken, the value 0.419 is implicitly cast to an int, and so zero comes back. You can see it by executing this:
DECLARE @ConversionCoeff MONEY = 1
DECLARE @IngQtyLackInGoods MONEY
DECLARE @IngQtyLack MONEY = 0.4190
DECLARE @GoodsCountable BIT = 0
-- return incorrect
SELECT CASE WHEN @GoodsCountable = 1
THEN CEILING(CEILING((@IngQtyLack * 10000.00) / @ConversionCoeff) / 10000.00)
ELSE CEILING((@IngQtyLack * 10000.00) / @ConversionCoeff) / 10000.00
END,
@IngQtyLack * 10000.00 AS Product,
(@IngQtyLack * 10000.00) / @ConversionCoeff AS Division,
CEILING((@IngQtyLack * 10000.00) / @ConversionCoeff) / 10000.00 AS FirstCeiling,
CEILING(CEILING((@IngQtyLack * 10000.00) / @ConversionCoeff) / 10000.00) AS SecondCeiling
