CODEWITHSUNDEEP
asp.netsqldivide-by-zero
April
April

Reputation: 85

Divide by zero error encountered

I'm having an divide by zero error when I launch this SQL query :

SELECT CONVERT(VARCHAR(11),CaptureTime,106) 'CaptureTime',
        Type,
        CASE WHEN Unit='g/L' THEN (data1* 0.02586) ELSE data1 END As data1,
        CASE WHEN Unit='g/L' THEN nullif(data2* 0.02586,0) ELSE data2 END As data2,
        CASE WHEN Unit='g/L' THEN nullif(data3* 0.02586,0) ELSE data3 END As data3,
        CASE WHEN Unit='g/L' THEN nullif(data1* 0.02586,0) / nullif(data2* 0.02586,0) ELSE (data1/data2) END As Ratio,
        CASE WHEN Unit='g/L' THEN 'ml/L' ELSE 'ml/L' END As data,
        CASE WHEN Unit='g/L' THEN (data4* 0.01129) ELSE data4 END As data4
FROM something 
where Type ='DATA'
and ID='" + Session["name"].ToString() + "'
Order By CONVERT(DateTime, CaptureTime,101) DESC

Upvotes: 2

Views: 2660

Answers (2)

Cyril Gandon
Cyril Gandon

Reputation: 17058

You can't compute your ratio if you have data2 being equal to zero, because you can't divive by zero, it will always output an error message.

I advise you to add a case :

CASE WHEN data2=0 
THEN 0
ELSE
    CASE WHEN Unit='g/L' 
    THEN (data1 * 0.02586) / (data2 * 0.02586) 
    ELSE data1/data2
    END 
END As Ratio,

Upvotes: 0

GarethD
GarethD

Reputation: 69769

The error is almost certainly coming from the second line here:

CASE WHEN Unit='g/L' THEN nullif(data1* 0.02586,0) / nullif(data2* 0.02586,0) " +
    "ELSE (data1/data2)" +  
    "END As Ratio,"

If the unit is not G/L and data2 is 0 you will get an error. Change it to:

CASE WHEN Unit='g/L' THEN nullif(data1* 0.02586,0) / nullif(data2* 0.02586,0) " +
    "ELSE (data1/NULLIF(data2, 0))" +  
    "END As Ratio,"

Upvotes: 3

Related Questions