SQL Server 2008 Question

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 ,
rustypootAsked:
Who is Participating?
 
Ephraim WangoyaConnect With a Mentor Commented:
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
 
RimvisConnect With a Mentor Commented:
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
 
Mark WillsConnect With a Mentor Topic AdvisorCommented:
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
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
Mark WillsConnect With a Mentor Topic AdvisorCommented:
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
 
Mark WillsConnect With a Mentor Topic AdvisorCommented:
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
 
rustypootAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.