Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Server 2008 Question

Posted on 2013-01-02
6
Medium Priority
?
416 Views
Last Modified: 2013-01-03
The SQL below is giving me an error below:

Msg 3623, Level 16, State 1, Line 1
An invalid floating point operation occurred.

How can I fix this?

CASE WHEN   ISNUMERIC(WT.text)<> 1 OR ISNUMERIC(HT.text)<> 1
THEN NULL
ELSE
 CASE WHEN (Year(getdate())-#tmpClient.BirthYearNum) > = 18    
   THEN Convert(decimal(6,2),(Power(Convert(decimal(6,2),HT.text),0.725) * Power(convert(decimal(6,2),convert(float,WT.text)/1000.0),0.425)) * 0.007184)    
  ELSE Convert(decimal(6,2),SQRT((Convert(decimal(6,2),HT.text) * (convert(decimal(6,2),convert(float,WT.text)/3600)))))    
 END  
END as BSA ,
0
Comment
Question by:rustypoot
[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
6 Comments
 
LVL 19

Assisted Solution

by:Rimvis
Rimvis earned 400 total points
ID: 38739797
Hi rustypoot,

ISNUMERIC does not guarantee that value can be successfully casted as a number.
You can implement custom function to check if the value is correct. Check this for reference:
http://classicasp.aspfaq.com/general/what-is-wrong-with-isnumeric.html
0
 
LVL 32

Accepted Solution

by:
Ephraim Wangoya earned 400 total points
ID: 38739941
Try this

CASE WHEN (WT.text LIKE '%[^0-9]%') AND (HT.text) LIKE '%[^0-9]%') THEN
  CASE WHEN (Year(getdate())-#tmpClient.BirthYearNum) > = 18  THEN 
      Convert(decimal(6,2),(Power(Convert(decimal(6,2),HT.text),0.725) *
         Power(convert(decimal(6,2),convert(float,WT.text)/1000.0),0.425)) * 0.007184)     
  ELSE 
      Convert(decimal(6,2),SQRT((Convert(decimal(6,2),HT.text) *  
         (convert(decimal(6,2),convert(float,WT.text)/3600)))))
  END
ELSE
  NULL
END as BSA

Open in new window

0
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 1200 total points
ID: 38740048
Unfortunately isnumeric can return a 1 for any of the number types - which includes money and symbols like +,- and $

Have you been able to identify the data causing the problem ?

Does it need to be float ?

And the "like" wont really work either... e.g.

select case when 'abd 123' LIKE '%[^0-9]%' then 'use it' else 'lose it' end as [like]
      ,case when isnumeric('$12,300.00')=1 then 'use it' else 'lose it' end as [money]

Open in new window


You can see from the above that either will cause havoc.
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 1200 total points
ID: 38740896
Pity you arent using SQL2012 because you could use :

-- http://msdn.microsoft.com/en-us/library/hh230993.aspx

SELECT 
    CASE WHEN TRY_CONVERT(float, 'test') IS NULL 
    THEN 'Cast failed'
    ELSE 'Cast succeeded'
END AS Result;

Open in new window


I think for your purposes, you will most likely need a function, or possibly a series of PATINDEX() functions to make sure you are dealing with numbers that will convert (cast) properly...

Also, if the results of a CASE is meant to be decimal(6,2) then your NULL condition should also be expressed as a decimal(6,2)

Please let us know if you can find the data (ie it is a data problem and not really a function problem) or if you need a user defined function.

Only problem with a user defined function is it will be scaler and that can be slowwwww...

Maybe the query could be expressed as a stored procedure ? We might have a few more options as a SP.
0
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 1200 total points
ID: 38741124
If you try to convert an inappropriate value, you normally expect a "conversion" failure, or, maybe an arithmetic overflow if the values are too big for decimal(6,2)

Your error is indicating something else...

Like a SQRT(-4) instead of SQRT(4), so might need to check for any negative values when you go through to check for data...
0
 

Author Closing Comment

by:rustypoot
ID: 38741222
Thanks everyone for your feedback! I appreciate it. <br /><br />After spending more time researching the issue, I found out that it was the POWER (Convert (6,2)) function that was causing this error! One of the users had entered the data that was longer than (6,2)! So, I modified the code from (6,2) to (30,2) and it worked! <br /><br />Thanks for all your time and feedback.
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

688 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