Divide by zero with SQL

Question

This query works fine for each and every test case if you choose the person's last name:

SELECT P.Patient_ID, P.Person_FirstName, P.Person_LastName
    , H.Height
    , W.Weight
    , CASE
        when  H.Height is not null then W.Weight / (H.Height * H.Height)
        else null
     END as BMI
FROM 
    VIEW_BillPatient P
    LEFT JOIN H on P.Patient_ID = H.Patient_ID
    LEFT JOIN W on P.Patient_ID = W.Patient_ID  

WHERE   
    P.Person_LastName = 'ZZtest'

This even works when H.Height is null or W.Weight is null.

Unfortunately, as soon as I take the WHERE clause off and try to run it for everybody, I get a divide by zero error.

SELECT P.Patient_ID, P.Person_FirstName, P.Person_LastName
    , H.Height
    , W.Weight
    , CASE
        when  H.Height is not null then W.Weight / (H.Height * H.Height)
        else null
     END as BMI
FROM 
    VIEW_BillPatient P
    LEFT JOIN H on P.Patient_ID = H.Patient_ID
    LEFT JOIN W on P.Patient_ID = W.Patient_ID

Error:

Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.

(Sub-queries H and W respectively return a Patient_ID and the last value for Height or Weight in the database, as usually there is more than one recorded (or null if none is found). The formula is to calculate Body Mass Index as seen in the code)

What am I doing wrong? (MS SQL SERVER 2008-R2)

Answer 1

You could use nullif(expr1,expr2).

Returns a null value if the two specified expressions are equal.

In this case, BMI will be null if height is 0 or null or if weight is null

SELECT P.Patient_ID, P.Person_FirstName, P.Person_LastName
    , H.Height
    , W.Weight
    , W.Weight / nullif(H.Height * H.Height, 0) as BMI
FROM 
    VIEW_BillPatient P
    LEFT JOIN H on P.Patient_ID = H.Patient_ID
    LEFT JOIN W on P.Patient_ID = W.Patient_ID  

WHERE   
    P.Person_LastName = 'ZZtest

... or simply change your case condition to explicitly avoid 0. You'll get null if height is 0 or null or if weight is null

SELECT P.Patient_ID, P.Person_FirstName, P.Person_LastName
    , H.Height
    , W.Weight
    , case when H.Height != 0 then W.Weight / (H.Height * H.Height) end as BMI
FROM 
    VIEW_BillPatient P
    LEFT JOIN H on P.Patient_ID = H.Patient_ID
    LEFT JOIN W on P.Patient_ID = W.Patient_ID  

WHERE   
    P.Person_LastName = 'ZZtest