how can this be?? - SQL Server Forums

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)

how can this be??

Author

Topic

djorre
Yak Posting Veteran

94 Posts

Posted - 2009-07-14 : 07:26:48

Anybody an idea what I do wrong because I guess this can't be?

quote:
SELECT PValue,

CASE
WHEN isnumeric(PValue)=1
THEN round(PValue,0)
ELSE PVAlue
END AS PValue

FROM batchhis

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to float.

quote:
SELECT PValue,
CASE
WHEN isnumeric(PValue)=1
THEN cast(PValue as int)
ELSE PVAlue
END AS PValue
FROM batchhis

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '\\COS..PROGRAM\JOURNALS\273.evt' to data type int.

djorre
Yak Posting Veteran

94 Posts

Posted - 2009-07-14 : 07:56:26

There seems to be no way for to do it...

I tried (found on internet)

THEN CONVERT(nvarchar(50),CONVERT(decimal(10,2),ValueField))

doesn't work either

Select cast(rtrim(case
when isnumeric(PValue) = 1 then
round(Pvalue,0)
else
PValue
End)
as int)
From batchhis

does not work

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-07-14 : 08:27:59

You have non-numeric data in that column, like \\COS..PROGRAM\JOURNALS\273.evt
When you do your case statement you are saying "make this numeric if you can, leave it as a string if you can't". Your data types have to be compatible in your case statement. Also, ISNUMERIC is very reliable (select isnumeric('1d5')).

Jim

djorre
Yak Posting Veteran

94 Posts

Posted - 2009-07-14 : 08:32:10

quote:
Originally posted by jimf

You have non-numeric data in that column, like \\COS..PROGRAM\JOURNALS\273.evt
When you do your case statement you are saying "make this numeric if you can, leave it as a string if you can't". Your data types have to be compatible in your case statement. Also, ISNUMERIC is very reliable (select isnumeric('1d5')).

Jim

what i want to do is:

round this if it is a number, leave it if it is text.
but that seems to be impossible.
the problem is not reliability of isnumeric function because when i do isnumeric(PValue) it gives correct booleans.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-14 : 08:35:38

quote:
Originally posted by jimf

You have non-numeric data in that column, like \\COS..PROGRAM\JOURNALS\273.evt
When you do your case statement you are saying "make this numeric if you can, leave it as a string if you can't". Your data types have to be compatible in your case statement. Also, ISNUMERIC is not very reliable (select isnumeric('1d5')).

Jim

Madhivanan

Failing to plan is Planning to fail

Ifor
Aged Yak Warrior

700 Posts

Posted - 2009-07-14 : 10:13:29

Your problem is that the result is being defined as a float and you are trying to put a varchar into it. You need to make sure all parts of the CASE produce the same datatype. eg:

CASE 
    WHEN ISNUMERIC(PValue)=1
    THEN CAST(ROUND(PValue, 0) AS varchar(20))
    ELSE PVAlue 
END AS PValue

djorre
Yak Posting Veteran

94 Posts

Posted - 2009-07-14 : 10:26:36

Aha indeed that is it! Thank you Ifor!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources