Solved

Apparant floating point error inconsistency

Posted on 2011-02-17
7
361 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
data stored like ????? in sql server column 19 54
Problem with email format from SQL DB 4 34
Run an action on recently added records to a table 13 61
SSMS Opening Mode 9 18
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…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

856 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