Solved

Apparant floating point error inconsistency

Posted on 2011-02-17
7
363 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Format Date fields 11 65
efficient backup report for SQL Server 13 82
semaphore timeout period has expired 1 33
SSIS Error on Bat File execute 2 16
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
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.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

752 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