Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401

    Unanswered: Miracle, Bug or My Ignorance ???

    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)
    The code above is giving an error
    Code:
    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 ???
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I can't seem to replicate the problem over here. Here is what I am using:

    Code:
    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?

  3. #3
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    dont know what is happening .. the code runs good on your tables ... gives an error on mine
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    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.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Cut and paste this, and tell me it doesn't work...

    Code:
    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
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    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.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Added that link to my SQL Server favorites folder....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Huh?!?! What, who me?

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

    -PatP

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    He mentioned that [cust code] is defined as float (not the he post the DDL or sample data...tsk...tsk...)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Yes I did pat ...
    Code:
    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

    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)
    Attached Files Attached Files
    Last edited by Enigma; 05-12-04 at 13:51.
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  11. #11
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    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 ....

    Code:
    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 ???
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  12. #12
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    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?

  13. #13
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Checked that out ... try using EM ... import/export wizard ;D
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  14. #14
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    bcp pubs..Temp_Upload_Table in temp_upload_table.dat -T -E -c -t"\t" -Smyserver

    ?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  15. #15
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    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.....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •