March 10, 2009 at 11:23 am
I am using SQL 2005 Developer Edition version 9.00.4035.00. When I run the following statement:
select cast(stranswer as real)
from question inner join answer
on question.questionid = answer.questionid
where questionnumber in
('1.0','2.0','3.0')
and IsNumeric(stranswer) = 1
and stranswer <> '-'
I get the following error:
Error converting data type varchar to real.
But, here is the weird part, if I run this modified version it works:
select stranswer into #tmptable
from question inner join answer
on question.questionid = answer.questionid
where questionnumber in
('1.0','2.0','3.0')
and IsNumeric(stranswer) = 1
and stranswer <> '-'
select cast(stranswer as real) from #tmptable
drop table #tmptable
Can anyone tell my why the first statement does not work? Is it a database setting?
March 10, 2009 at 3:28 pm
I'm thinking there is a value in the stranswer column of the question table where the questionnumber is not '1.0', '2.0', or '3.0' that cannot be converted to a real value. When you select those values into a temp table, then that value is not there and does not cause the error.
I'm not 100% sure on that, but that is my hunch.
Nate
Nate TeWinkel
UFS Inc. - DBA / Operations Analyst / Programmer
March 10, 2009 at 3:52 pm
Tip: Use the ISNUMERIC function to find the non-numeric values:
DECLARE @t TABLE (txt VARCHAR(100))
INSERT INTO @t VALUES ('1.2')
INSERT INTO @t VALUES ('2a')
SELECT * FROM @t WHERE ISNUMERIC(txt) != 1
Greets
Flo
March 11, 2009 at 6:32 am
The questionnumber is what I am filtering by so it should be doing an exact string match. The conversion is being done on the stranswer column. What I don't understand is when I copy the values to a temp table, then it the conversion works. Does this mean that the conversion in the select is happening before the IsNumeric check in the where clause?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply