divide by zero, but divisor is not zero

[bartsimpson]

I have written a view that lists data, and divides one column by another to give me a new column. This works for the first 16 rows of data, but when it hits row 17, I get a divide by zero error. There is no zero in the divisor column.

The SQL is below :

SELECT     volts AS [Voltage Ave], [Noise sum] / [noise count] AS Noise
FROM         dbo.data

This always happens on row 17, but I see no real difference between row 16 and row 17 data.

 

[dbomrrsm]

worth doing a select from the table where either of the columns you are using are equal to 0 or are null.

Might show you which row is causing the problem.

DBomrrsm

 

[bartsimpson]

There are no zeros in either column, and there is no null data.

 

[dbomrrsm]

Create this function

CREATE FUNCTION [dbo].[IsNumeric]
(
@Text varchar(255)
)
RETURNS BIT
AS


BEGIN
DECLARE @Letters VARCHAR(255)
DECLARE @Index INT
DECLARE @Max INT
DECLARE @Pass BIT
SET @Letters = ' 01234567890'
SET @Max = LEN(@Text)
SET @Index = 0
SET @Pass = 1
WHILE @Index < @Max AND @Pass = 1
BEGIN
SET @Index = @Index + 1
IF NOT @Letters LIKE '%' + SUBSTRING(@Text, @Index, 1) + '%' SET @Pass = 0
END
RETURN(@Pass)
END

Then use it to select rows

SELECT *
FROM dbo.data
WHERE dbo.IsNumeric([Noise sum]) = 0

If all the chars in the field are numeric the result of the function will return 1 if there is anything else in there it returns 0

Also try this on the other column.

DBomrrsm

 

[bartsimpson]

DBomrrsm,

Thanks for this.

I've created the function, and had to add a "." to the @Letters variable - the numbers are real, not int.

I got no rows back, as you may expect. As soon as row 17 is displayed, then I get an error. The query is actually looking at a view, that has grouping in it to get this data. Could the problem be here ?

 

[despierto]

DBomrrsm,

that's really awesome how you did that. I'm just wondering one thing? Why bother? SQL Server has ISNUMERIC built in...

 

[MatthewPeters]

bartsimpson

Looking up the following two commands in BOL may or may not be of interest to you.

set arithignore
set arithabort

matt

 

[dbomrrsm]

despierto

I changed a function called AlphaNumeric which has the line in:

SET @Letters = ' abcdefghijklmnopqrstuvwxyz01234567890'

I dont believe there is a ISALPHANUMERIC function built into SQL Server.

Just tailored for this posts needs.

also think its good to show different ways of doing things rather than just using built in functionality as it gets the thought process going and feel it helps people learn - I wouldnt look at how isnumeric works but doing it the above way at least you can see the code and understand what is going on.

Also helps with devising other methods of overcoming problems with SQL.

Thanks for your comments.

Cheers

DBomrrsm

 

[bartsimpson]

mdp,

Thanks for the tip - but it seems that these commands are for stored procedures, and I'm trying to write a view. The SET command isn't supported in a view.

I'm still struggling with this, though. It seems that I can't perform a divide with the result of a grouping, although I have tried a different field, and that seems OK. This is driving me slowly nuts - it's got to be something really stupid, but I suppose I must be even more stupid ....

 

[despierto]

Two things:
DBomrrsm, there isn't an ISALPHANUMERIC, but there is pattern matching, kinda like regular expressions:

Declare @something varchar(25)
set @something = 'Hahaha!'
if @something like '%[^a-z0-9]%' Print 'Bad' ELSE Print 'Good'

Bart, if you are designing your view in SQL Enterprise manager with the Create new view window, the query designer does not support a lot of things that are valid in views... It just isn't smart enough. (Although SET isn't allowed in a view period)

SELECT  volts AS [Voltage Ave], 
	Noise = CASE isnull([noise count], 0)
	  WHEN 0 then 0 
	  ELSE [Noise sum] / [noise count] END
FROM    dbo.data

 

[pweegar]

bartsimpson:
The ISNUMERIC function should be able to tell you whether or not your data is numeric. Look at BOL and do a search for ISNUMERIC. If your data passes the ISNUMERIC test and still fails, you need to look at your logic and see what is happening.

 

[ESquared]

Just be aware that IsNumeric has its failings, as ',' will test out as true because it can be converted to a money datatype (but not int for example).

In my opinion the best method is

MyIsNumeric = NOT LIKE '%[^0-9]%'

as despierto already pointed out.

-------------------------------------
A sacrifice is harder when no one knows you've made it.