Divide by zero error encountered.

Question

Divide by zero error encountered.

smer

SSCommitted

Points: 1684
More actions
December 14, 2014 at 5:26 am

#313073

Hi,
I am finding an error while i am dividing value. Some times Val1 is not available and some time Val2 is not available in my case. I need if Val1 is not available then division will be null ,but if value Val1 is available and Val2 is not available then get Val1.
ISNULL(VAL1/NULLIF((VAL2),0),0)
Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 1

farrukhhameed786 (12/14/2014)
Hi,
I am finding an error while i am dividing value. Some times Val1 is not available and some time Val2 is not available in my case. I need if Val1 is not available then division will be null ,but if value Val1 is available and Val2 is not available then get Val1.
ISNULL(VAL1/NULLIF((VAL2),0),0)
Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.

Quick thought, only wrap the VAL2 in ISNULL for 1 as null replacement value

😎

DECLARE @VAL1 INT = NULL;

DECLARE @VAL2 INT = NULL;

SELECT @VAL1/ISNULL(@VAL2,1) AS RESULT;

SET @VAL1 = 10;

SET @VAL2 = NULL;

SELECT @VAL1/ISNULL(@VAL2,1) AS RESULT;

SET @VAL1 = NULL;

SET @VAL2 = 10;

SELECT @VAL1/ISNULL(@VAL2,1) AS RESULT;

SET @VAL1 = 120;

SET @VAL2 = 10;

SELECT @VAL1/ISNULL(@VAL2,1) AS RESULT;

Results

RESULT

-----------

NULL

RESULT

-----------

10

RESULT

-----------

NULL

RESULT

-----------

12

smer SSCommitted Points: 1684 More actions · Answer 2

Hi SS

Thanks ! but how to apply in one sql query ?

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

farrukhhameed786 (12/14/2014)
Hi SS
Thanks ! but how to apply in one sql query ?

Post the query that you want to use it in.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 4

farrukhhameed786 (12/14/2014)
Hi SS
Thanks ! but how to apply in one sql query ?

The code shows the behaviour of the same NULL handling

VAL1/ISNULL(VAL2,1)

for all scenarios, simply replace the formula you posted with this one.

😎

serg-52 SSCrazy Eights Points: 9913 More actions · Answer 5

Replacing (VAL2) with ISNULL(VAL2,1) will do the trick.

But wait a moment, VAL1/NULLIF((VAL2),0) should never raise 'divide by zero' error. Are you sure it's exactly the expression which causes an error?

smer SSCommitted Points: 1684 More actions · Answer 6

Hi

Please Below the query i need to use condition

I need to use in below query how to use

SELECT
ISNULL(EVT.VAL1/NULLIF((EVT.VAL2),0),0) AS ITEM_ID
FROM
EVENT1 EVT
WHERE NAME IN ('JOHN','PATRICK')

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 7

farrukhhameed786 (12/15/2014)
Hi
Please Below the query i need to use condition
I need to use in below query how to use
SELECT
ISNULL(EVT.VAL1/NULLIF((EVT.VAL2),0),0) AS ITEM_ID
FROM
EVENT1 EVT
WHERE NAME IN ('JOHN','PATRICK')

You need to wrap ISNULL around NULLIF((EVT.VAL2),0) , like:

ISNULL(NULLIF((EVT.VAL2),0), x)

Replace x by the non-zero value you want to use.

Other option is to use a CASE statement to handle the case where NULLIF((EVT.VAL2),0) is 0.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 8

farrukhhameed786 (12/15/2014)
Hi
Please Below the query i need to use condition
I need to use in below query how to use
SELECT
ISNULL(EVT.VAL1/NULLIF((EVT.VAL2),0),0) AS ITEM_ID
FROM
EVENT1 EVT
WHERE NAME IN ('JOHN','PATRICK')

Consider this example

😎

DECLARE @EVENT1 TABLE

(

VAL1 INT NULL

,VAL2 INT NULL

,[NAME] VARCHAR(10) NOT NULL

);

INSERT INTO @EVENT1 (VAL1,VAL2,[NAME])

VALUES (NULL,NULL,'PATRICK')

,(1200,NULL,'JOHN')

,(NULL,10,'PATRICK')

,(NULL,0,'PATRICK')

,(100,0,'PATRICK')

,(120,10,'JOHN');

SELECT

EVT.VAL1/ISNULL(NULLIF(EVT.VAL2,0),1) AS ITEM_ID

FROM @EVENT1 EVT

WHERE EVT.NAME IN ('JOHN','PATRICK');

Results

ITEM_ID

-----------

NULL

1200

NULL

100

12

smer SSCommitted Points: 1684 More actions · Answer 9

smer

SSCommitted

Points: 1684

December 15, 2014 at 12:38 pm

#1764781

Thank you SS ...

your Kind help

MMartin1 One Orange Chip Points: 27795 More actions · Answer 10

Eirikur Eiriksson (12/15/2014)
farrukhhameed786 (12/15/2014)
Hi
Please Below the query i need to use condition
I need to use in below query how to use
SELECT
ISNULL(EVT.VAL1/NULLIF((EVT.VAL2),0),0) AS ITEM_ID
FROM
EVENT1 EVT
WHERE NAME IN ('JOHN','PATRICK')
Consider this example
😎
DECLARE @EVENT1 TABLE
(
VAL1 INT NULL
,VAL2 INT NULL
,[NAME] VARCHAR(10) NOT NULL
);
INSERT INTO @EVENT1 (VAL1,VAL2,[NAME])
VALUES (NULL,NULL,'PATRICK')
,(1200,NULL,'JOHN')
,(NULL,10,'PATRICK')
,(NULL,0,'PATRICK')
,(100,0,'PATRICK')
,(120,10,'JOHN');
SELECT
EVT.VAL1/ISNULL(NULLIF(EVT.VAL2,0),1) AS ITEM_ID
FROM @EVENT1 EVT
WHERE EVT.NAME IN ('JOHN','PATRICK');
Results
ITEM_ID
-----------
NULL
1200
NULL
NULL
100
12

I just want to point out that in the above results a division by zero against a number returns the numerator (top number) . This is fine if this is want you want. Normally I like to see divide by zero produce an error because it does not make logical sense.

----------------------------------------------------

smer SSCommitted Points: 1684 More actions · Answer 11

Hi SS

Actually the problem is e.g Some times the Val2 is not calculated by meters,but the VAL1 calculates every day in rare case Val1 not calculated, that is why equation needs like that. You are correct "Normally i like to see divide by zero produce an error because it doe snot make logical sense"

smer SSCommitted Points: 1684 More actions · Answer 12

Hi SS

Actually the problem is e.g Some times the Val2 is not calculated by meters,but the VAL1 calculates every day in rare case Val1 not calculated, that is why equation needs like that. You are correct "Normally i like to see divide by zero produce an error because it doe snot make logical sense"

MMartin1 One Orange Chip Points: 27795 More actions · Answer 13

As long as the business users understand the calculation and they are fine with it, that is what matters. Ideally if there are values missing I like to return null in the backend, and a descriptive message in the front end of the report (like "Unknown due to field x being blank").

----------------------------------------------------

DonlSimpson SSCertifiable Points: 7043 More actions · Answer 14

NULLIF((EVT.VAL2), 0)

Maybe I'm just missing something here, but how can the above EVER return a zero?

Don Simpson

I'm not sure about Heisenberg.