Solved

Apparant floating point error inconsistency

Posted on 2011-02-17
7
364 Views
Last Modified: 2012-05-11
The following query runs without error:

select batchno, pid , 2010 - convert(float,right(Q8_3_2DOB,4))
from
[Census2010-CLEAN].[dbo].[PeopleQuestionnaire-2]
where
charindex(' ', right(Q8_3_2DOB,4)) = 0 and Q8_3_2DOB is not null

yet, this one gives the error  Error converting data type nvarchar to float :

select batchno, pid
from
[Census2010-CLEAN].[dbo].[PeopleQuestionnaire-2]
where
2010 - convert(float,right(Q8_3_2DOB,4)) <> 21 and
charindex(' ', right(Q8_3_2DOB,4)) = 0 and Q8_3_2DOB is not null



Don't they do the same conversion on the same data?  The only difference from my point of view is that in the second query the conversion happens in the where clause.

 

Will somebody please explain this apparent inconsistency.
0
Comment
Question by:sib_user
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 13

Expert Comment

by:devlab2012
ID: 34918021
check the values in column Q8_3_2DOB. There must be some non-numeric values.
0
 
LVL 13

Assisted Solution

by:devlab2012
devlab2012 earned 100 total points
ID: 34918048
In your first query, you are using the CAST operation after filtering the records i.e. after WHERE condition is evaluated.
But in second query, first the CAST operation takes place without filtering the records because the CAST is inside where condition. So it is applied on all records and records are filtered after that.
0
 
LVL 41

Accepted Solution

by:
Sharath earned 400 total points
ID: 34918074
Can you run this and check if you are still getting the error.
SELECT * 
  FROM (SELECT batchno, 
               pid, 
               2010 - CONVERT(FLOAT,RIGHT(Q8_3_2DOB,4)) col1 
          FROM [Census2010-CLEAN].[dbo].[PeopleQuestionnaire-2] 
         WHERE CHARINDEX(' ',RIGHT(Q8_3_2DOB,4)) = 0 
               AND Q8_3_2DOB IS NOT NULL) t1 
 WHERE col1 <> 21

Open in new window

0
Database Solutions Engineer FAQs

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller single-server environments.

 

Author Comment

by:sib_user
ID: 34918295
devlab2012,
Thanks for your suggestions.  I agree with you that there must be some non numeric characters in the year portion of the dob field and have tried filtering the out by including the following condition in the whereclause:
  and right(Q8_3_2DOB,4) not like '%[a-zA-Z]%'

yet that made no difference.  What I need is a condition that says 'where the last characters of the dob field are only numeric characters'

Sarath_123:
I have tried your suggestion and get the same error unfortunately.
0
 

Author Comment

by:sib_user
ID: 34919088
Experts:

This works:

select batchno, pid
from
[Census2010-CLEAN].[dbo].[PeopleQuestionnaire-2]
where
2010 - convert(float,right(Q8_3_2DOB,4)) <> 21 and
charindex(' ', right(Q8_3_2DOB,4)) = 0 and Q8_3_2DOB is not null
and isnumeric(right(Q8_3_2DOB,4)) = 1
0
 

Author Comment

by:sib_user
ID: 34919126
I made a mistake awarding the points here: my intention was to split the points between devlab's two answers.  Is there any way to undo my error?  
0
 
LVL 41

Expert Comment

by:Sharath
ID: 34919538
Request the moderator.
0

Featured Post

Ready to get started with anonymous questions?

It's easy! Check out this step-by-step guide for asking an anonymous question on Experts Exchange.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question