Divide By Zero Error in WHERE Clause
-
SELECT Field1, Field2, etc...
FROM tbl_Purchase
WHERE (Field1 / Field2) + (Other Calculations) > 0
I have a SQL statement that is similar to the above. Field2 can be zero sometimes. This is how the data is stored and I have to do comparison based on the value of Field1 divided by Field2. Is there someway to re-write this query so that it'll still work? I need some way to do something like this:
IF (Field2 > 0) THEN
(Field1 / Field2) + (Other Calculations) > 0
ELSE
0 + (Other Calculations) > 0
and use that expression in the WHERE clause. But, TSQL doesn't work with IF statements. I've tried using the CASE statement, but I can't get it to work either. Does anyone know how to do this? Thanks!
-
I think the Case statement is the way to go. Something like this:
SELECT Field1, Field2, etc...
FROM tbl_Purchase
WHERE ((case field2
when 0 then 0
else field1/field2
end)
+ (other calculations)) > 0
Regards.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
If you are going to use a case statement in your WHERE clause, I would put one in your SELECT as well to warn you that field2 is zero.
-
So, if field2 is 0, you want to replace this with a 1 to avoid the division by zero error.
Try this:
field1 / ISNULL(NULLIF(field2, 0), 1)
The idea is that if field2 = 0, then convert the zero to a NULL (that's what NULLIF does) - then use ISNULL to replace the null with a 1
/Kenneth
-
Kenneth, if field1 is anything other than zero, your solution won't produce the required result: instead it will produce (field1 + (other calculations)), in the case where field2 is zero.
However, this is a devious technique that I'd not come across before – I like it
Phil
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Hmm... how do you mean?
If field1 is 2 and field2 is 0, you have 2/0 which is illegal.
What you want then is 2/1 which is 2.
Agreed, this is logically the same as 'field1 + (other calcs)' - but this is exactly what we want in this case.
It works as intended allright.
What I showed was only how to avoid division by zero errors regarding field2, it is assumed that all other fields can never have a zero value.
However, it's easy to apply it on every field that may contain a zero, but for calculation purposes should then be switched to a one.
The template is the same;
ISNULL(NULLIF(myValue, 0), 1)
=;o)
/Kenneth
-
Sorry to nit-pick, but just got up at 4am to watch England exit Euro 2004 on penalties, so I'm on the war-path!
Quoting from the original post, the required result in the case of field2 being zero is "0 + (Other Calculations)", not "field1 + (other calcs)".
It's going to be a long day
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Ah, my bad.
Didn't read the question close enough...
In that case,
selectCASE field2 WHEN 0 THEN 0
ELSE (field1 / field2) END
+ ( other calcs )
FROM ....
should do just as required..
/Kenneth
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply