SQLTeam.com | Weblogs | Forums

Avoid exponential values


#1

hi team ,

in my folllowing simple query today i got some inappropriate exponential values like -3.10891025752239E+38 due to this i got error in my application.

following is the query :

select sum(col1)- sum(col2) as diff
from table_test
where id = @id -- here some id comes from proc

and in col2 there are some exponential values that causes error, please help me to avoid these exponential values getting in my query. i want that no exponential values should be selected by query. i tried isnumeric() but it didn't work.

please suggest.


#2

How do you know they are exponential values? What is the math populating col1 and col2? Is there an exponential calculation there somewhere? Or, do you just mean "big" (big doesn't have to be exponential).


#3

when i checked in col2 i found -3.10891025752239E+38 value , yes this is very big value or u can say very smaal because this is negative value, but i dont want to include these kind of values that contains E.


#4

then just add a where clause:

where abs(val) < (some threshold)


#5

Where did this data come from? This looks like the value should be zero but Microsoft helped you out.


#6

may be there is some error in data loading but i need to avoid this value only after analysis of source from where data is loading i will take further action but now i need to som first aid and restrict my query so that no issue will occur.


#7

I believe gbritton's answer should get you there


#8

looks like the data type for the col1, col2 is float ?


#9

Or VARCHAR? (but maybe that would not implicitly convert to floating point arithmetic?)