Solved

SQL Server 2008 Question

Posted on 2013-01-02
6
384 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
6 Comments
 
LVL 19

Assisted Solution

by:Rimvis
Rimvis earned 100 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:
ewangoya earned 100 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 300 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
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.

 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 300 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 300 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

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

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…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

910 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

23 Experts available now in Live!

Get 1:1 Help Now