[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Error converting data type nvarchar to float

news.microsoft.com

3/22/2007 6:49:00 PM

When I run this sql statement I get the above error. If I change the 'not
in' to an 'in', it works fine. If I run against each table separately it
works fine. Why can't I cast the nvarchar to a float and use the 'not in'?
I want to find all the records in the first table not in the second table.
I have to use the cast.

Bill

Select Distinct EmpNo, FName, LName from DuesSA Where empno <> '' and

cast(EmpNo as double precision) not in (select distinct cast(employeenumber
as double precision)

from Member

Where EmployerNo = 10 And employeeNumber is not null)
4 Answers

AlterEgo

3/22/2007 7:01:00 PM

0

Bill,

It appears you have a row where the empno or employeenumber has characters
other than valid numerics. You can try the IsNumeric function, but it
doesn't catch everything. Check BOL.

There must be a reason, but why are you casting both as double?

-- Bill

"Bill Gower" <billgower@charter.net> wrote in message
news:Obm9MLLbHHA.4656@TK2MSFTNGP06.phx.gbl...
> When I run this sql statement I get the above error. If I change the 'not
> in' to an 'in', it works fine. If I run against each table separately it
> works fine. Why can't I cast the nvarchar to a float and use the 'not
> in'? I want to find all the records in the first table not in the second
> table. I have to use the cast.
>
> Bill
>
> Select Distinct EmpNo, FName, LName from DuesSA Where empno <> '' and
>
> cast(EmpNo as double precision) not in (select distinct
> cast(employeenumber as double precision)
>
> from Member
>
> Where EmployerNo = 10 And employeeNumber is not null)
>
>
>
>


news.microsoft.com

3/22/2007 7:43:00 PM

0

I am using the cast because the field may or may not have leading zeros and
the field that I am comparing to may one time have the leading zeros and
another time not. No the field does not have any non numeric characters in
it. I can run a select statement with a cast against each table
individually. It has to do with the not in vs. the in. I will tell you
that in this case all the records in table 1 are found in table 2 so maybe
it has to do with no records being returned.

"AlterEgo" <alterego55@dslextreme.com> wrote in message
news:OI3QAeLbHHA.4888@TK2MSFTNGP06.phx.gbl...
> Bill,
>
> It appears you have a row where the empno or employeenumber has characters
> other than valid numerics. You can try the IsNumeric function, but it
> doesn't catch everything. Check BOL.
>
> There must be a reason, but why are you casting both as double?
>
> -- Bill
>
> "Bill Gower" <billgower@charter.net> wrote in message
> news:Obm9MLLbHHA.4656@TK2MSFTNGP06.phx.gbl...
>> When I run this sql statement I get the above error. If I change the
>> 'not in' to an 'in', it works fine. If I run against each table
>> separately it works fine. Why can't I cast the nvarchar to a float and
>> use the 'not in'? I want to find all the records in the first table not
>> in the second table. I have to use the cast.
>>
>> Bill
>>
>> Select Distinct EmpNo, FName, LName from DuesSA Where empno <> '' and
>>
>> cast(EmpNo as double precision) not in (select distinct
>> cast(employeenumber as double precision)
>>
>> from Member
>>
>> Where EmployerNo = 10 And employeeNumber is not null)
>>
>>
>>
>>
>
>


davewilliamson55555@hotmail.com

3/22/2007 8:23:00 PM

0

Do you get zero records back for the following queries?

select EmpNo from DuesSA
where
replace(empno, '0', '') <> rtrim(ltrim(empno))


select EmployeeNumber from Member
where
replace(EmployeeNumber, '0', '') <> rtrim(ltrim(EmployeeNumber))


If not then there are spaces in the data and that counts as characters
too.

AlterEgo

3/22/2007 9:42:00 PM

0

Bill,

Just a couple more ideas:

1. Is the datatype of the employeenumber column also a non-numeric datatype.
If so, you are not accounting for an empty string as you are with empno as
follows: where EmployerNo = 10 And isnull(employeeNumber,'') <> ''

2. Did you select the entire tables with the conversion? The offending row
might be excluded in tests with different comparisons. For example:

select cast(...) from Member where EmployerNo = 10 might use an index to
filter out the records based upon EmployerNo and only select non-offending
rows.
select cast(...) from Member where EmployerNo = 10 and empno <> '' might do
a table scan and run into an offending row.

If no one else has any ideas you might want to submit the bug to Microsoft:
http://blogs.msdn.com/sqlblog/archive/2006/09/24/7...

-- Bill


"Bill Gower" <billgower@charter.net> wrote in message
news:%23xUB$oLbHHA.1388@TK2MSFTNGP05.phx.gbl...
>I am using the cast because the field may or may not have leading zeros and
>the field that I am comparing to may one time have the leading zeros and
>another time not. No the field does not have any non numeric characters in
>it. I can run a select statement with a cast against each table
>individually. It has to do with the not in vs. the in. I will tell you
>that in this case all the records in table 1 are found in table 2 so maybe
>it has to do with no records being returned.
>
> "AlterEgo" <alterego55@dslextreme.com> wrote in message
> news:OI3QAeLbHHA.4888@TK2MSFTNGP06.phx.gbl...
>> Bill,
>>
>> It appears you have a row where the empno or employeenumber has
>> characters other than valid numerics. You can try the IsNumeric function,
>> but it doesn't catch everything. Check BOL.
>>
>> There must be a reason, but why are you casting both as double?
>>
>> -- Bill
>>
>> "Bill Gower" <billgower@charter.net> wrote in message
>> news:Obm9MLLbHHA.4656@TK2MSFTNGP06.phx.gbl...
>>> When I run this sql statement I get the above error. If I change the
>>> 'not in' to an 'in', it works fine. If I run against each table
>>> separately it works fine. Why can't I cast the nvarchar to a float and
>>> use the 'not in'? I want to find all the records in the first table not
>>> in the second table. I have to use the cast.
>>>
>>> Bill
>>>
>>> Select Distinct EmpNo, FName, LName from DuesSA Where empno <> '' and
>>>
>>> cast(EmpNo as double precision) not in (select distinct
>>> cast(employeenumber as double precision)
>>>
>>> from Member
>>>
>>> Where EmployerNo = 10 And employeeNumber is not null)
>>>
>>>
>>>
>>>
>>
>>
>
>