CODEWITHSUNDEEP
sqlsql-server-2005overflow
csotelo
csotelo

Reputation: 1485

Arithmetic overflow error converting nvarchar to data type numeric when the value is 0

I have the following query returns resulting me Null or Zero:

SELECT TOP 1 ISNULL([jul-12],0) FROM Table_tmp 
WHERE ID = 123250838

but when I add a condition asking if the column value is zero causes overflow error:

SELECT TOP 1 ISNULL([jul-12],0) FROM Table_tmp 
WHERE ID = 123250838
AND [jul-12] <> 0

The data type of the column is FLOAT

exec sp_help 'Table_tmp'
jun-12  float   no  8   53      NULL    yes (n/a)   (n/a)   NULL

I tried with the functions CONVERT () and CAST () but with the same result.

But when the value of the column [Jul-12] is nonzero, it works without errors. Why does this happen?

Upvotes: 0

Views: 7698

Answers (3)

nbpeth
nbpeth

Reputation: 3148

I just ran across this problem and solved it by throwing an "IS NOT NULL" clause into "WHERE" on the column that I was using cast()

Upvotes: 0

Roman Badiornyi
Roman Badiornyi

Reputation: 1539

This is because of CAST () specification. The result data type will be as in the second parameter. To avoid this you can use COALESCE function which determine result data type by first parameter, also benefit of this function is that it's ANSI standard (instead of ISNULL). you can look for this function here: COALESCE MSDN usage in your case is: COALESCE(jul-12],0) - this will return first not null value.

Upvotes: 0

Pandian
Pandian

Reputation: 9126

Try like below... may be it will help you...

SELECT TOP 1 ISNULL([jul-12],0) FROM Table_tmp 
WHERE  Str([jul-12], 25, 5) <> '0'

Upvotes: 1

Related Questions