### Question :

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.

### 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;
```