Reputation: 115
Why is this giving me a divide by zero error? I would like this to return the count of the records in the database with stated parameters. Thanks for the help.
SELECT COUNT(*)
FROM dbcrms.ccars.ci_submission_history
where crms_dt = '2016-12-31'
and totalliabilities <> null or totalliabilities <> 0
and mra_required = 'Yes'
and committedexposure / totalliabilities <= 1
I am using SQL Server 2014
Upvotes: 1
Views: 70
Reputation: 38073
You cannot guarantee the order in which your clauses will be evaluated, so committedexposure / totalliabilities
may be evaluated before totalliabilities <> 0
A workaround setting 0
to null
with nullif()
:
SELECT COUNT(*)
FROM dbcrms.ccars.ci_submission_history
where crms_dt = '2016-12-31'
and totalliabilities <> 0
and mra_required = 'Yes'
and committedexposure / nullif(totalliabilities,0) <= 1
And <> null
should probably be is not null
, which will be true if totalliabilities <> 0
, so it is redundant.
Also, be aware that and
takes precedence over or
as far as logical operators go. It would be best to wrap your each side of your or
so that it is clear what you want to accomplish.
Upvotes: 6