Arithmetic overflow error converting expression to data type money.

[luvmy2Js]

What am I doing wrong in the below statement:

CASE
WHEN NOT CAST(CONVERT(nvarchar, DecryptByKey(a.[1003-Ref Salary])) as MONEY) > 0 THEN NULL
WHEN NOT c.Name IS NULL THEN (a.[PP salary-Comm] * CAST(y.[Annual Conversion] as money))
ELSE (CAST(CONVERT(nvarchar, DecryptByKey(a.[1003-Ref Salary])) as MONEY) * CAST(y.[Annual Conversion] as MONEY))
END,


Thanks a million!!

 

[TheBugSlayer]

Sounds like a data issue rather. Can you check that the values you are trying to convert are numeric and fall within the range of the datatype (MONEY -922,337,203,685,477.5808 to 922,337,203,685,477.5807).


MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[luvmy2Js]

Yes they do fall within the data type of money, however I did notice the field is in the database a "float"? Could that be the issue?

 

[TheBugSlayer]

A value may well fall within the limits of both MONEY and FLOAT or REAL datatype, you should always use the smallest datatype that can hold the greater value in your data range. That been said, try and test with a small subset of your data, to see which row fails the test. Make sure that your decryption also returns the correct numeric value; you could run a simple SELECT DecryptByKey(a.[1003-Ref Salary]) FROM YourTable a and look at the values...Then try to run a SELECT CONVERT(nvarchar, DecryptByKey(a.[1003-Ref Salary])) FROM YourTable a and so on. An overflow error means the value returned does not fit in the target data type. Take it from there.

Good luck.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[luvmy2Js]

Thanks!