CASE expression returns wrong value when using CEILING

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 d

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;

StackExchange Database Administrators Q#136379, answer score: 11