Forum Discussion
SanyaSan
May 19, 2022Copper Contributor
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 @Go...
GregLow
May 26, 2022MVP
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
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