CODEWITHSUNDEEP
sqlsql-server
Daniel Bailey
Daniel Bailey

Reputation: 115

Why is this giving me a divide by zero error

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

Answers (1)

SqlZim
SqlZim

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

Related Questions