Divide by zero error encountered.

Question

Divide by zero error encountered.

Viewing 5 posts - 16 through 19 (of 19 total)

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

DonlSimpson SSCertifiable Points: 7043 More actions · Answer 1

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

I'd be really hesitant to default a "1" into a calculation like this. That a produces a quotient that may not reflect reality. I think it is much wiser to return a null or some other message in the case where VAL2 = 0.

Don Simpson

I'm not sure about Heisenberg.

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

NULLIF((EVT.VAL2), 0)
Maybe I'm just missing something here, but how can the above EVER return a zero?

It would not, but that is what the user wanted. That is to prevent a divide by zero and just return Val1, and why I pointed out to user that a zero value would not return an error as normally expected.

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

DonlSimpson SSCertifiable Points: 7043 More actions · Answer 3

MMartin1 (12/16/2014)
NULLIF((EVT.VAL2), 0)
Maybe I'm just missing something here, but how can the above EVER return a zero?
It would not, but that is what the user wanted. That is to prevent a divide by zero and just return Val1, and why I pointed out to user that a zero value would not return an error as normally expected.

Didn't the OPs original code include the NULLIF? Is so, how was he getting a divide by zero error?

Don Simpson

I'm not sure about Heisenberg.

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

DonlSimpson (12/16/2014)
MMartin1 (12/16/2014)
NULLIF((EVT.VAL2), 0)
Maybe I'm just missing something here, but how can the above EVER return a zero?
It would not, but that is what the user wanted. That is to prevent a divide by zero and just return Val1, and why I pointed out to user that a zero value would not return an error as normally expected.
Didn't the OPs original code include the NULLIF? Is so, how was he getting a divide by zero error?

In the OPs original code it was ISNULL(VAL1/NULLIF((VAL2),0),0), the result is always 0 unless both values are <>0 and NOt NULL, no division by zero possible.

😎

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

Eirikur Eiriksson (12/16/2014)
DonlSimpson (12/16/2014)
MMartin1 (12/16/2014)
NULLIF((EVT.VAL2), 0)
Maybe I'm just missing something here, but how can the above EVER return a zero?
It would not, but that is what the user wanted. That is to prevent a divide by zero and just return Val1, and why I pointed out to user that a zero value would not return an error as normally expected.
Didn't the OPs original code include the NULLIF? Is so, how was he getting a divide by zero error?
In the OPs original code it was ISNULL(VAL1/NULLIF((VAL2),0),0), the result is always 0 unless both values are <>0 and NOt NULL, no division by zero possible.
😎

Exactly, and the OP was asked how this could come about but no response to this question. (and why I implied the divide by zero error should not necessarily be disguised). I guess he got divide by zero initially and wanted to know if this code piece could prevent that, just my wild guess.

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