Solved

Apparant floating point error inconsistency

Posted on 2011-02-17
7
359 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
  • 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 40

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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

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 40

Expert Comment

by:Sharath
ID: 34919538
Request the moderator.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

760 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now