Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Server 2008 Question

Posted on 2013-01-02
6
Medium Priority
?
430 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 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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
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 …
Screencast - Getting to Know the Pipeline
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…

877 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