Bug, Miracle or Ignorance - 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 2000 Forums

Transact-SQL (2000)

Bug, Miracle or Ignorance

Author

Topic

The Enigma
Posting Yak Master

179 Posts

Posted - 2004-05-12 : 10:41:42

[code]
select

'[Cust Code]' ColName
,RowId
from
Temp_Upload_Table
where

[cust code]
not in ( select Convert(Float,KUNNR) from KNVV_View
where isnumeric(kunnr) = 1)
[/code]
The code above is giving an error
[code]
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
[/code]

The [cust code] column is float .

The subquery select Convert(Float,KUNNR) from KNVV_View where isnumeric(kunnr) = 1 when executed alone gives correct result. When i put is as subquery it bombs ...

Is this a miracle, a bug or is there something I need to know ???

Enigma

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-05-12 : 13:21:43

hmmm....what datatype is [Cust code]?

OS

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts

Posted - 2004-05-12 : 14:57:34

quote:
Originally posted by mohdowais

hmmm....what datatype is [Cust code]?

OS

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

Looks like [Cust Code] is varchar.

The Enigma,
Run is query and see if it returns anything:

select * from Temp_Upload_Table where [cust code] like '%[a-z]%'

Daniel
SQL Server DBA

The Enigma
Posting Yak Master

179 Posts

Posted - 2004-05-12 : 17:59:01

As I said :

The [cust code] column is float

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts

Posted - 2004-05-12 : 18:32:27

quote:
Originally posted by The Enigma

As I said :

The [cust code] column is float

Is the data from the KNVV_View table large?

If the subquery works by itself then try this just for giggles:

-- -- -- -- -- -- -- -- --
select distinct Convert(Float,KUNNR) KUNNR_Float into #cake from KNVV_View where isnumeric(kunnr) = 1
select '[Cust Code]' ColName, RowId from Temp_Upload_Table where [cust code] not in (select * from #cake)
-- -- -- -- -- -- -- -- --

-- Also run this: (should return an error cause #cake should have created KUNNR_Float as a float)
-- -- -- -- -- -- -- -- --
select * from #cake where KUNNR_Float like '%[a-z]%'
-- -- -- -- -- -- -- -- --

Daniel
SQL Server DBA

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-05-12 : 19:37:57

Not all IsNumeric() values can be converted to float.

You will have to exclude where KUNNR not like '$%' and KUNNR not like '%,%'

select isnumeric('$123')
1
select isnumeric('12,3')
1

select convert(float,'$123')
Error converting data type varchar to float.
select convert(float,'12,3')
Error converting data type varchar to float.

The Enigma
Posting Yak Master

179 Posts

Posted - 2004-05-13 : 08:52:40

The [cust code] column is float .

The subquery select Convert(Float,KUNNR) from KNVV_View where isnumeric(kunnr) = 1 when executed alone gives correct result. When i put is as subquery it bombs ...

Will I need to repeat that again to the next poster ?????

Enigma

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-05-13 : 10:22:19

Whoa, you gotta keep your cool, mate. Thanda thanda...cool, cool!

I think kselvia got it right, ISNUMERIC will usually return true for values with commas in them, but a cast to float will fail. Do you have any commas or currency signs in the values in KUNNR?

OS

SamC
White Water Yakist

3467 Posts

Posted - 2004-05-13 : 10:46:25

OS - Does this answer your question?

quote:
Originally posted by The Enigma
The subquery select Convert(Float,KUNNR) from KNVV_View where isnumeric(kunnr) = 1 when executed alone gives correct result. When i put is as subquery it bombs ...

The Enigma
Posting Yak Master

179 Posts

Posted - 2004-05-13 : 14:32:20

http://www.dbforums.com/t997537.html

X002548
Not Just a Number

15586 Posts

Posted - 2004-05-13 : 14:40:20

quote:
Originally posted by The Enigma

Will I need to repeat that again to the next poster ?????

Dude....run out of whiskey?

Brett

8-)

SamC
White Water Yakist

3467 Posts

Posted - 2004-05-13 : 17:24:40

So Enigma, did the dbforums thread solve your problem? I didn't see a post from you saying so, but it does look like the right things were being considered?

There are some other ways to code that query if the NOT IN / NULL problem is tweaking the exeuction plan incorrectly. You could left outer join the subquery and select rows that're null on the right.

Sam

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts

Posted - 2004-05-13 : 21:00:09

quote:
Originally posted by The Enigma

The [cust code] column is float .

The subquery select Convert(Float,KUNNR) from KNVV_View where isnumeric(kunnr) = 1 when executed alone gives correct result. When i put is as subquery it bombs ...

Will I need to repeat that again to the next poster ?????

Enigma

If you needed help then you should've either tried to explain more or tried the examples we posted and then told us that those didnt work either and then let us know the results. Perhaps instead of yelling you could have posted more or posted all your code like on the other forum or at the very least you could have just tried what we suggested.

Rude... just simply rude...

Daniel
SQL Server DBA

The Enigma
Posting Yak Master

179 Posts

Posted - 2004-05-14 : 00:58:24

Well ... I wasnt getting anywhere in this forum ...

quote:

And i did not post the code and data earlier coz i thought there might be some reasonable explanation for this ....

I think this would answer your question Daniel ...
Moreover, I would not go on posting data on the internet unless and until it is absolutely necessary or someone in the forum asks for it. If you see in the dbforums post .. you would see that i gave the data only after Brett asked for it ... So go on and call me all the names you like ... i am not affected ... And yeah .. I am rude ... very simply rude ...

The Enigma
Posting Yak Master

179 Posts

Posted - 2004-05-14 : 01:04:13

Sam .. what i am interested in knowing is why that happened .... so as to avoid the problem in the future ... have got what i wanted by coding the query in a different way.

SamC
White Water Yakist

3467 Posts

Posted - 2004-05-14 : 10:46:27

Yeah. I'd like to know too. I'd play with the code myself, but the fish here have bigger Sams to fry. If you do figure it out, let me know.

Long ago, I had a NOT IN () that wouldn't work unless I used DISTINCT:

... NOT IN (SELECT DISTINCT ColumnA FROM ...)

it made no sense, but it fixed the problem.

Sam

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-05-14 : 18:23:54

NOT IN () implies distinct, hence a work table, which does not happen with the straight SELECT. I do not know exactly why the error occurs, but since your error is;

Error converting data type varchar to numeric.

not

Error converting data type varchar to float.

it seems the problem does not lie with the explicit convert(float,KUNNR) but in a different implicit conversion being done by SQL server in an attempt to map different datatypes. That does not happen in the code you posted so I suspect the problem is inside the view itself.

Also something to remember isnumeric() not only returns 1 for values containing $ and , but also those containing trailing tabs, any leading spaces, E and D notation (some of which can be converted to float, some can not) (I admit that does not appear to be the problem here it's good to know)

Ken

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources