question

BobLarson-6601 avatar image
0 Votes"
BobLarson-6601 asked TomCooper-6989 answered

Multiple CASTs WHY?

I am starting a new job working with some previous SSRS reports and the person previous to me has, in the dataset SQL, several spots where they have

CAST(SUM(CAST(MyFieldNameHere As Decimal(21,4))) As Decimal(21,4))

The original field is Decimal(19,4) in the table but I don't see why they would have to cast it twice to 21,4. Any good reason that anyone can think of?

We're using SQL 2017 or 2017 Compatibility, not sure exactly.

sql-server-transact-sqlsql-server-reporting-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

TomCooper-6989 avatar image
0 Votes"
TomCooper-6989 answered

You don't need both CAST's. The interior cast will not change the final result in any way. So you just need
CAST(SUM(MyFieldNameHere) AS Decimal(21,4))

The SUM documentation at https://docs.microsoft.com/en-us/sql/t-sql/functions/sum-transact-sql?view=sql-server-ver15 states that the return type of a SUM of a decimal(p,s) will be decimal(38,s). So changing the type before the sum from decimal(19,4) to decimal(21,4) won't change the result.

Tom


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.