Error converting data type varchar to float.
-
Hi,
I am trying to fire the following query on my database. The field ver_clarify is a varchar field. But inspite of me using convert() & isnumeric() functions to eliminate any nonnumeric values from the query, I am still getting the following error when I fire the query.
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to float.
select distinct ctrl.title ,ctrl.type, ctrl.name ,ctrl.win_id,win.ver_clarify ,win.id
from table_control_db ctrl, table_window_db win, mtm_privclass1_control_db2 mtm
where ctrl.control2window_db = win.objid
and ctrl.objid = mtm.control_db2privclass
and isnumeric(win.ver_clarify)=1
and convert(float,win.ver_clarify) =
( select max (convert(float,win_sub.ver_clarify))
from table_window_db win_sub
where win_sub.id=win.id
and isnumeric(win_sub.ver_clarify)=1
and convert(float,win_sub.ver_clarify) <= 9.0
)
Can anyone please help me understand where my query is failing ?
Why is the query picking up nonnumeric values eventhough I have used isnumeric functions ?
-
>>and convert(float,win_sub.ver_clarify) <= 9.0
You have this in your WHERE clause of the sub-query. I think you are expecting thisp art of the WHERE *not* to be evaluated if IsNumeric() is zero, but you have no control over how the conditions in the WHERE clause are evaluated ...just because this line is *after* the IsNumeric() check doesn't mean IsNumeric() will be evaluated first.
Therefore you still end up trying to convert non-numerics to floats.
Instead of selecting directly from the table, I'd select from a Derived Table that applies the IsNumeric() check and limits the resultset to numerics only.
-
I am not sure whether thats the reason why the query is failing.
I created aview with only numeric values.
create view view_table_window_db
as select * from table_window_db where isnumeric(ver_clarify)=1
I replaced the table in the original query with this view. But still its giving me the same problem !!!
-
Does the column contain formatted numbers ? Try these 2 selects, the result may surprise you ...
Select IsNumeric('1,000')
Select Convert(float, '1,000')
-
Just a quick note :
isnumeric ('.') = 1 and it can't be converted to a float... might be worth checking it out.
-
To confirm the above points, I fired the following queries.
select distinct '*' + ver_clarify + '*' from table_window_db where isnumeric(ver_clarify)=1
*1.0*
*10.1*
*12.0*
*12.5*
*3.3*
*3.4*
*4.0*
*4.5*
*5.0*
*6.0*
*7.0*
*9.0*
Also I fired one more query to ensure that whether everything can be converted to float...
select distinct convert(float,ver_clarify) from table_window_db where isnumeric(ver_clarify)=1
1.0
3.2999999999999998
3.3999999999999999
4.0
4.5
5.0
6.0
7.0
9.0
10.1
12.0
12.5
The results of these queries are surprising me... Its able to convert all values to float. But in the initial query (The one where this is giving problem), its failing !!!
-
Unfortunately, ISNUMERIC is not reliable check because of values '.', '+' and '-' which return 1, but can not be converted into numbers.
However, this is not your problem, as it seems according to your last check. I suppose that PW gave you the right answer. If there are any values in ver_clarify that can not be converted to float, your original query can have problems with it. Try to build the query in two steps... in first step just select the value as it is in the table (varchar or whatever), applying the WHERE ISNUMERIC condition and put it into a temporary table. Then in another query perform the conversion to float and all the comparisons.
If this works, try other step. Select all the rows in which ISNUMERIC = 1, without any other conditions, give alias to this query or define it as a view and use it in your query instead of the original table. I'm not sure it will work, but I think it should.
HTH, Vladan
-
ISNUMERIC behaves very funny at times. SQL Server MVP Steve Kass has posted the following in the public MS newsgroups. Enjoy!
SELECT
ISNUMERIC('$') AS Money_1
,ISNUMERIC('2d3') AS Float_1
, ISNUMERIC('$+,') AS Money_2
Money_1 Float_1 Money_2
----------- ----------- -----------
1 1 1
(1 row(s) affected)
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Hi Friends,
Thanks a lot for all your help. I tried using view & copying values fullfilling isnumeric condition to a temporary table. But both tricks did not work.
Finally I got my problem solved by some strange method which does not have any logical reasons, I guess.
I fired the following query instead of the initial query.
- select distinct ctrl.title ,ctrl.type, ctrl.name ,ctrl.win_id,win.ver_clarify ,win.id
- from table_control_db ctrl, table_window_db win, mtm_privclass1_control_db2 mtm
- where ctrl.control2window_db = win.objid
- and ctrl.objid = mtm.control_db2privclass
- and isnumeric(win.ver_clarify)=1
- and convert(float,win.ver_clarify) = ( select max (convert(float,win_sub.ver_clarify))
- from table_window_db win_sub
- where convert(int,win_sub.id)=win.id
- and isnumeric(win_sub.ver_clarify)=1
- and convert(float,win_sub.ver_clarify)<= 9.0
- )
Please note that the "id" field in table_window_db is an int field. I do not understand why it needs a conversion to int !!!
Also what difference it makes if I try convert function on the field ?
Does anybody know why this is happening ?
Thanks you all once again for your suggestions & help.
-
Just being curious... if you rewrite the original query to use JOIN syntax and put the first ISNUMERIC into JOIN instead of WHERE, do you still get the error? Like this:
SELECT distinct ctrl.title ,ctrl.type, ctrl.name ,ctrl.win_id,win.ver_clarify,win.id
FROM table_control_db ctrl
JOIN table_window_db win ON ctrl.control2window_db = win.objid AND isnumeric(win.ver_clarify)=1
JOIN mtm_privclass1_control_db2 mtm ON ctrl.objid = mtm.control_db2privclass
WHERE
convert(float,win.ver_clarify) =
(select max(convert(float,win_sub.ver_clarify))
from table_window_db win_sub
where win_sub.id=win.id
and isnumeric(win_sub.ver_clarify)=1
and convert(float,win_sub.ver_clarify)<= 9.0)
Probably yes, but who knows 🙂 And I suppose that table_window_db.id is identity column of that table... or?
-
You need to use CASE in your where clause. This sample works:
create table test (Num nvarchar(40))
GO
insert test values ('10')
insert test values ('20')
insert test values ('qa')
insert test values ('30')
insert test values ('b')
GO
select * FROM test
GO
SELECT CAST(Num as int) FROM test WHERE (CASE WHEN ISNUMERIC(Num)=1 THEN CAST(Num as int) ELSE 0 END)>10
GO
-- DROP TABLE test
-
Hi vladan, peterhe,
Both the SQL's didnt work out.
I fired the following SQL's as per your advice.
SELECT distinct ctrl.title ,ctrl.type, ctrl.name ,ctrl.win_id,win.ver_clarify,win.id
FROM table_control_db ctrl
JOIN table_window_db win ON ctrl.control2window_db = win.objid AND isnumeric(win.ver_clarify)=1
JOIN mtm_privclass1_control_db2 mtm ON ctrl.objid = mtm.control_db2privclass
WHERE
convert(float,win.ver_clarify) =
(select max(convert(float,win_sub.ver_clarify))
from table_window_db win_sub
where win_sub.id=win.id
and isnumeric(win_sub.ver_clarify)=1
and convert(float,win_sub.ver_clarify)<= 9.0)
select distinct ctrl.title ,ctrl.type, ctrl.name ,ctrl.win_id,win.ver_clarify ,win.id
from table_control_db ctrl, table_window_db win, mtm_privclass1_control_db2 mtm
where ctrl.control2window_db = win.objid
and ctrl.objid = mtm.control_db2privclass
and isnumeric(win.ver_clarify)=1
and cast(win.ver_clarify as float) =
( select max (cast(win_sub.ver_clarify as float))
from table_window_db win_sub
where win_sub.id=win.id
and isnumeric(win_sub.ver_clarify)=1
and cast(win_sub.ver_clarify as float) <= 9.0
)
But both the SQl's gave me the same error
The id field is not an identity field. Its a normal int field with the following property (Copied & pasted from sp_help result )
id int no 4 10 0 no (n/a) (n/a) NULL
I am wondering how converting this int field again to int solved the problem !!!
-
Hmmm... so the "id" column allows NULL. Are there any NULL values in this column? What is your ANSI_NULLS setting? I always work with ANSI_NULLS ON, so I'm not too sure about the behavior when it is OFF. I tested something similar to your query in QA with various combinations of data values, but failed to reproduce the error. Obviously, either a special combination of values is required, or some setting influences the behavior.
Could you post a small sample of data on which it can be reproduced?
-
There is one more strange thing which I have observed today.
I created another table which is a copy of the table_window_db table. Using the following SQL
select *
into #table_window_db
from table_window_db
But I observed that the query is working fine with this new table... So I think it has got to do something other than the direction in which we are thinking...
I searched in the syscomments table to check whether there is any other object which is using these tables... But all I could find was some stored procedures & views.
Thogh the problem is solved by using the earlier SQl (which I explained 2 days back), I am very curious to know why this error is happening...
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply