Error converting data type nvarchar to numeric

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Error converting data type nvarchar to numeric

Author

Topic

SL_sky
Starting Member

7 Posts

Posted - 2009-04-28 : 02:51:31

I am using SQL Server 2005 and run reporting services.

I get the error "Error converting data type nvarchar to numeric" when execute.

If there is no value for parameter @FromValue or @ToValue, it will return the
error above.

My code is as below:

SELECT
tmp.ClientNRIC,
tmp.ClientDisplayName,
tmp.ItemName,
tmp.PerformedYear,
CAST(AVG(tmp.ResultValue) AS NUMERIC(12,2)) AS MeanValue
FROM #TempResult tmp
WHERE tmp.RowNumber = 1
GROUP BY
tmp.ClientNRIC, tmp.ClientDisplayName, tmp.ItemName, tmp.PerformedYear
HAVING (CAST(AVG(tmp.ResultValue) AS numeric(12,2)) >= CAST(@FromValue AS NUMERIC(12,2)) OR @FromValue Is Null)
AND (CAST(AVG(tmp.ResultValue) AS numeric(12,2)) <= CAST(@ToValue AS NUMERIC (12,2)) OR @ToValue Is Null)

Thanks in advance

aprichard
Yak Posting Veteran

62 Posts

Posted - 2009-04-28 : 02:58:50

Hi,

You just check the parameter with ISNULL() and then compare with as given below

CAST(ISNULL(@FromValue,0) AS NUMERIC(12,2))

SL_sky
Starting Member

7 Posts

Posted - 2009-04-28 : 03:14:38

Hi aprochard, thanks for reply.
but the code you provide still not working. It return the same error to me.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-04-28 : 03:40:37

what's the data type of tmp.ResultValue , @FromValue & @ToValue ? Does any of these contains non-numeric chars ?

KH
[spoiler]Time is always against us[/spoiler]

SL_sky
Starting Member

7 Posts

Posted - 2009-04-28 : 03:45:17

All data type of tmp.ResultValue , @FromValue & @ToValue are varchar

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-04-28 : 04:00:35

quote:
Does any of these contains non-numeric chars ?

If it does contains non-numeric chars .. what are you going to do about this ?

KH
[spoiler]Time is always against us[/spoiler]

SL_sky
Starting Member

7 Posts

Posted - 2009-04-28 : 04:03:42

khtan, do you mean, if it does contain non-numeric chars, then sure i will get this error?
any solution?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-04-28 : 04:11:39

yes. Of course you can't convert non numeric to numeric.

quote:
any solution?

First of all . . you should be answering the question that i asked before. What do you want to do with such records ?
skip those records ? treat it as 0 or 1 or ?

if you want to skip those records then just add a where clause to it

WHERE isnumeric(tmp.ResultValue) = 1

treat it as 0 or 1 ?
use case statement on it. Example

AVG(case when isnumeric(tmp.ResultValue) = 1 then tmp.ResultValue else 0 end)

KH
[spoiler]Time is always against us[/spoiler]

SL_sky
Starting Member

7 Posts

Posted - 2009-04-28 : 04:30:37

oic...let me input some background
I need to calculate the average result by year.
2 parameters but optional. The parameters are the search criteria for the average result range.

I think the problem should be in the HAVING clause...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-28 : 04:54:45

Note that isnumeric() is not fully reliable

select isnumeric('12d2'),isnumeric('.'),isnumeric('$'),isnumeric(',')

Madhivanan

Failing to plan is Planning to fail

SL_sky
Starting Member

7 Posts

Posted - 2009-04-30 : 01:26:40

Thanks all, i get the way to solve this!~

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources