Error converting data type nvarchar to numeric.

Question

Error converting data type nvarchar to numeric.

danielfountain

SSCarpal Tunnel

Points: 4229
More actions
February 28, 2014 at 2:32 am

#304539

Hi all,
I have a query that looks something like this:
select
a.Id,
cast(isnull(B.answer,0) as numeric(32,2))
from
tableA A
INNER JOIN
TableB on A.ID = B.ID
WHERE
A.Team = 'Team1'
Now with this i am getting an error on Error converting data type nvarchar to numeric. I have searched and searched and cant find any data that should not convert. Here is the bit i dont understand. If i put it into a temp table like this:
select
a.Id,
b.answer
into #tempy
from
tableA A
INNER JOIN
TableB on A.ID = B.ID
WHERE
A.Team = 'Team1'
select
cast(isnull(answer,0) as numeric(32,2))
from #tempy
It works!!!!
I cant find any differences in config between tempdb and the main database. I have looked at the system table on tempdb to check to make sure the column was created as an Nvarchar - it is. I even doubled checked to make sure that i remembered the order of precidence of the operators correctly.
I just cant work out 1) what data is causing the issue 2) why this isnt causing a problem when moved into dev.
Thanks in Advance
Dan

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Are there any answers for teams other than Team1 which have non-numeric values? If so, that's probably the cause. The case could be occuring on rows which would later be filtered out.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

danielfountain SSCarpal Tunnel Points: 4229 More actions · Answer 2

GilaMonster (2/28/2014)
Are there any answers for teams other than Team1 which have non-numeric values? If so, that's probably the cause. The case could be occuring on rows which would later be filtered out.

Hi,

Thanks for the quick response. I am still a little confused. Some people seem to think that the where would get executed prior to the Select. I cant find a definitive answer online.

I have just tried this

select id,

cast(isnull(B.answer,0) as numeric(32,2))

from (

select

a.Id,

B.answerfrom

tableA A

INNER JOIN

TableB on A.ID = B.ID

WHERE

A.Team = 'Team1'

) DATA

But this is still not working. Surely if it was the where clause causing this issue then this would get round the issue.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

danielfountain (2/28/2014)
Surely if it was the where clause causing this issue then this would get round the issue.

No.

All you've done there is add an extra layer which the parser will remove. SQL is a declarative language, say what you want, the engine figures out the how of getting it to you.

Did you check the data? Are there values for other teams which are not numeric?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

danielfountain SSCarpal Tunnel Points: 4229 More actions · Answer 4

Hi Gail,

There will be non numerics (i have simplified the query a little).

I have tried this:

select

a.Id,

cast(

CASE WHEN A.Team = 'Team1' then

isnull(B.answer,0) else 0 end as numeric(32,2))

from

tableA A

INNER JOIN

TableB on A.ID = B.ID

WHERE

A.Team = 'Team1'

To get round it but its still causing issues.

Dan

kapil_kk SSC-Insane Points: 21316 More actions · Answer 5

HI Daniel,

I think you are not clear what Gail is asking.

what he is saying that for other than 'Team1' do you have non numeric data in the table....if that exists then it may causing that conversion error..

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

danielfountain SSCarpal Tunnel Points: 4229 More actions · Answer 6

Hi,

Thanks for the message. I did understand Gail - perhaps my response wasnt clear. Yes other entries may have non numericals.

That is why i tried putting a case into the sum put 0 for any entries that aren't in the where clause. So if the select is evaluated first then the case statement will handle the non numericon teamA stuff that is removed in the where clause.

Thanks

Dan