SQL Server – CASE Expression Returns Wrong Value with CEILING

datatypessql servert-sqltype conversion

I've run into an issue where a CASE expression does not return what I expect.

As a test, I added a decimal variable and ran the same CASE expression against it and it works fine, returning the results as I would expect (rounding the value up when IsGun=1. But when I run that same CASE expression against another decimal value, it always returns the value with the CEILING() function and never returns the original value.

Here is the SQL code:

DECLARE @Num decimal(8,2);
    set @Num = 12.54;
    WITH PQ AS
    ( 
        SELECT 
            UPC, 
            Price1, 
            DBID,
            AVG(Price1) OVER (PARTITION BY UPC) AS Price1Avg
        FROM
            vProducts_PriceQty_Union
    )
    SELECT 
        PQ.UPC,
        PQ.Price1,
        PQ.Price1Avg,
        (CASE WHEN p.IsGun = 1 THEN CEILING(@Num) ELSE @Num END) AS UsingVar,
        CAST(
            (CASE WHEN P.IsGun = 1 THEN CEILING(PQ.Price1Avg) ELSE PQ.Price1 END)
             AS NUMERIC(8,2))
        AS PriceAdj,
        PQ.DBID,
        P.IsGun
    FROM
        PQ
     INNER JOIN
        products P ON PQ.UPC = P.UPC

Here is a snippet of the results:

UPC             Price1      Price1Avg   UsingVar    PriceAdj    DBID  IsGun
942000899195    14.9900     14.990000   12.54       15.00       1       0
980420671300    29.9900     29.990000   12.54       30.00       1       0
980420671310    29.9900     29.990000   12.54       30.00       1       0
980426713020    29.9900     29.990000   12.54       30.00       1       0
980426713120    29.9900     29.990000   12.54       30.00       1       0
000998622130    319.0000    319.000000  13.00       319.00      1       1
000998624730    314.0000    314.000000  13.00       314.00      1       1
000998624970    419.0000    419.000000  13.00       419.00      1       1
008244284754    1015.0000   1015.000000 13.00       1015.00     2       1
010633012288    267.0000    267.000000  13.00       267.00      6       1

And here is the data it comes from vProducts_PriceQty_Union:

UPC             Price1  Price2  Quantity    DBID
942000899195    14.9900 0.0000  2.00        1
980420671300    29.9900 0.0000  3.00        1
980420671310    29.9900 0.0000  1.00        1
980426713020    29.9900 0.0000  2.00        1
980426713120    29.9900 0.0000  1.00        1

As you can see from the first five, where IsGun = 0, the first CASE expression using the fixed variable returns the UsingVar value as what we would expect, 12.54. And for the last five, it also returns the value we would expect, 13.

But in the second CASE expression (exactly the same logic), the PriceAdj uses the CEILING function on every single one of them, regardless of whether IsGun = 1 or not.

Why isn't the query returning the expected results?

In some of the tables used for the union view the data types for Price1 and Price2 were smallmoney and decimal(8,2). I have since changed them all to be decimal(8,2), but that did not affect the results.

Best Answer

To reproduce the problem:

SELECT *, (CASE
    WHEN IsGun=1 THEN CEILING(Price1Avg)
    ELSE Price1 END)
FROM (
    SELECT UPC, IsGun, Price1,
           AVG(CAST(Price1 AS numeric(8, 2))) OVER (PARTITION BY UPC) AS Price1Avg
    FROM (
        VALUES ('A', 0, 14.99),
               ('B', 0, 29.99),
               ('C', 1, 319.00),
               ('D', 1, 314.00)
        ) AS x(UPC, IsGun, Price1)
    ) AS sub;

What happens here is that CEILING(PQ.Price1Avg) produces a numeric(38, 0).

According to the documentation, the output type of CEILING() is of the same base datatype as the input, although the scale (the number of decimals) may change, which is what happens here.

  • The AVG() function, in my tests, returns numeric(38, 6).
  • The CEILING() function on that column, however, outputs numeric(38, 0):

To verify:

SELECT CEILING(CAST(123.45 AS numeric(38, 6)))

As a workaround, you could explicitly convert the output of the CEILING() function, which should give you the correct results:

SELECT *, (CASE
    WHEN IsGun=1 THEN CAST(CEILING(Price1Avg) AS numeric(8, 2)) -- Explicit CAST.
    ELSE Price1 END)
FROM (
    SELECT UPC, IsGun, Price1,
           AVG(CAST(Price1 AS numeric(8, 2))) OVER (PARTITION BY UPC) AS Price1Avg
    FROM (
        VALUES ('A', 0, 14.99),
               ('B', 0, 29.99),
               ('C', 1, 319.00),
               ('D', 1, 314.00)
        ) AS x(UPC, IsGun, Price1)
    ) AS sub;
Related Question