Miracle, Bug or My Ignorance ??

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)

The code above is giving an error

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

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 ?I can't seem to replicate the problem over here. Here is what I am using:

create table temp_upload_table
([cust code] float,
rowid int)

create table KNVV_View
(KUNNR varchar(10))

insert into KNVV_View values ('o')
insert into KNVV_View values ('hello')
insert into KNVV_View values ('3.2')
insert into KNVV_View values ('1')

insert into temp_upload_table values (3.2, 1)
insert into temp_upload_table values (2.3, 2)

Am I missing something?|||dont know what is happening .. the code runs good on your tables ... gives an error on mine|||There was a thread a little while ago, maybe February that I remember had a similar problem. Danged if I can find it, now, but it may not apply. In the thread, it was found that some of the older notations for numbers (like 2.0e05 for 200,000) were making life miserable for the guy with the question. I think Pat Phelan may have been in on that thread, but I can't be too sure. That was a few too many beers ago.|||Cut and paste this, and tell me it doesn't work...

USE Northwind
GO

SET NOCOUNT ON

create table temp_upload_table
([cust code] float,
rowid int)

create table KNVV_View
(KUNNR varchar(10))

insert into KNVV_View values ('o')
insert into KNVV_View values ('hello')
insert into KNVV_View values ('3.2')
insert into KNVV_View values ('1')

insert into temp_upload_table values (3.2, 1)
insert into temp_upload_table values (2.3, 2)

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)
GO|||Found the thread I was after. Here it is:

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

I think Pat was up a bit late last night (or early this morning), so he may not be able to reply for a while.|||Added that link to my SQL Server favorites folder...|||Huh?!?! What, who me?

Did anyone check to see if 1 = IsNumeric([cust code])?

-PatP|||He mentioned that [cust code] is defined as float (not the he post the DDL or sample data...tsk...tsk...)|||Yes I did pat ...

CREATE TABLE [KNVV] (
[VTWEG] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[VKORG] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[VKGRP] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[VKBUR] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SPART] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MANDT] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[KVGR5] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[KVGR4] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[KVGR2] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[KVGR1] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[KUNNR] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[KDGRP] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BZIRK] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

create view KNVV_View as
select distinct VKGRP
,VKBUR
,SPART
,MANDT
,KVGR5
,KVGR4
,KVGR2
,KVGR1
,KUNNR
,KDGRP
,BZIRK
from KNVV
where BZIRK <> '999999'
and kvgr1 <> '999'

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE TABLE [Temp_Upload_Table] (
[RowId] [int] IDENTITY (1, 1) NOT NULL ,
[AOP-Value] [float] NULL ,
[AOP-Volume] [float] NULL ,
[Cust Code] [float] NULL
) ON [PRIMARY]
GO

Brett .. bcp in the data and tell me this works ;)

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)|||And i did not post the code and data earlier coz i thought there might be some reasonable explanation for this ... and hey this works on the same data ...

select * from Temp_Upload_Table where convert(varchar(20),Cust_Code) not in ( select convert(float,(select KUNNR where isnumeric(kunnr) = 1)) from KNVV_View )

Any explanations ?|||Hmm. Having a bit of trouble BCP-ing the temp_upload_table file in. I must be missing something obvious.

bcp pubs..Temp_Upload_Table in temp_upload_table.dat -T -E -c -t\t -Smyserver

is giving me:
SQLState = 37000, NativeError = 170
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '-'.

anyone else getting this, or is this because of the big bowl of stupid flakes I had this morning?|||Checked that out ... try using EM ... import/export wizard ;D|||bcp pubs..Temp_Upload_Table in temp_upload_table.dat -T -E -c -t"\t" -Smyserver

?|||Enterprise Manager? Pfft! I can't stand Enterprise Manager. It worked, though. ;-) And I am now getting the error. See if I get any results, though....|||OK, I have a piece of this figured out. It looks from the query plan that when you use "not in", the optimizer is checking for nulls. This check is not necessary when you are only interested in "in". I think that the implicit comparison for null is what is getting you. This apparently works:

SELECT '[Cust Code]' ColName
, RowId
FROM Temp_Upload_Table
WHERE [cust code]
not in (
select isnull (Convert(Float, KUNNR), 0) from KNVV_View
where isnumeric(kunnr) = 1

)|||Are you talking about the nulls in [cust code] column|||I think the optimizer is more worried about nulls returned by the subquery. I think if there is a null in the not in list, SQL Server may get confused. Something about that three way logic. This may be why the optimizer if pulling the possibility aside in the query plan.
What confuses me, though, is why it is not doing that extra "side comparison" for queries that simply use "IN (select...)". It may be that in a query with "in (...)", the nulls in the list are simply tossed, since null compared with anything returns nothing. You, however have a "not in (...)" in your query, so the ghost of DeMorgan may be haunting you.
Make any better sense?|||The funny thing here is that KUNNR in KNVV_View table does not have nulls ... so why is that happening ...|||Probably the optimizer does not want to risk it, so it has to check.