CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012
Mincho Minchev
Mincho Minchev

Reputation: 222

SELECT with CASE fail when aggregate function is used

I have the following t-sql query.

DECLARE @Test TABLE(
    Points INT
    ,PointsOf INT
)
INSERT INTO @Test
VALUES (3,12),(2,12),(3,12),(11,12),(12,12),(5,12),(0,12)

DECLARE @Decimal TINYINT = 2


SELECT  
    CASE @Decimal
        WHEN 0 THEN CAST(CAST(SUM(Points) AS DECIMAL(18,0)) / NULLIF(SUM(PointsOf), 0) * 100 AS DECIMAL(18,0))
        WHEN 1 THEN CAST(CAST(SUM(Points) AS DECIMAL(18,1)) / NULLIF(SUM(PointsOf), 0) * 100 AS DECIMAL(18,1))
        WHEN 2 THEN CAST(CAST(SUM(Points) AS DECIMAL(18,2)) / NULLIF(SUM(PointsOf), 0) * 100 AS DECIMAL(18,2))
    END  AS Score
FROM @Test

I have an variable @Decimals. When the variable is 0 I need my query to return score in XX format, when its 1 in XX.X format and when its 2 in the XX.XX format. What happen here is that the CASE enter multiple THEN clauses. When the query above is executed i get 44.86 as result which is correct, but when i change the @Decimals valiable to 0 i get a result 44.00 which is incorect. Its suppose to return just 44 without decimals. The same thing happen when i have @Decimals at 1, it returns 44.90 when it has to be 44.9.

Does any one know why this happen?

Upvotes: 1

Views: 89

Answers (2)

codingbadger
codingbadger

Reputation: 43994

As jarlh quite rightly points out the case statement is converting this to the smallest possible data type to hold all the result types. (See the MSDN documentation, specifically the Return Types section)

From my point of view, this is merely a display issue. You want to display 44.9 and not 44.90 (note the trailing zero) when the @decimal variable is set to one.

One way to do this is to add an additional cast to varchar. It's not pretty and not something I would recommend doing but if you insist on doing formatting and UI type things in SQL Server then what can you do?

DECLARE @Test TABLE(
    Points INT
    ,PointsOf INT
)
INSERT INTO @Test
VALUES (3,12),(2,12),(3,12),(11,12),(12,12),(5,12),(0,12)

DECLARE @Decimal TINYINT = 1


SELECT  
    CASE @Decimal
        WHEN 0 THEN cast(Cast(CAST(SUM(Points) AS DECIMAL(18,0)) / NULLIF(SUM(PointsOf), 0) * 100 as decimal(18,0)) as varchar(10))
        WHEN 1 THEN cast(Cast(CAST(SUM(Points) AS DECIMAL(18,1)) / NULLIF(SUM(PointsOf), 0) * 100 as decimal(18,1))as varchar(10))
        WHEN 2 THEN cast(Cast(CAST(SUM(Points) AS DECIMAL(18,2)) / NULLIF(SUM(PointsOf), 0) * 100 as decimal(18,2))as varchar(10))
    END  AS Score
FROM @Test

Upvotes: 4

jarlh
jarlh

Reputation: 44776

I don't know how MS Server works, but according to the ANSI SQL spec, a column always has one specific data type, which in this case is decimal(20,2), i.e. the smallest data type that can store all of the case's different result types.

Upvotes: 4

Related Questions