Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Apparant floating point error inconsistency

Posted on 2011-02-17
7
Medium Priority
?
367 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 400 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 1600 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

578 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