case expression allows you to write queries that avoid division by zero (called exception avoidance).
This example divides the total_sales column for each book by the advance column. The query results in division by zero when the query attempts to divide the total_sales (2032) of title_id MC2222 by the advance (0.00):
select title_id, total_sales, advance, total_sales/advance from titles
title_id total_sales advance ------- ----------- --------- ------ BU1032 4095 5,000.00 0.82 BU1111 3876 5,000.00 0.78 BU2075 18722 10,125.00 1.85 BU7832 4095 5,000.00 0.82 Divide by zero occurred.
You can use a case expression to avoid this by not allowing the zero to figure in the equation. In this example, when the query comes across the zero, it returns a predefined value, rather than performing the division:
select title_id, total_sales, advance, "Cost Per Book" = case when advance != 0 then convert(char, total_sales/advance) else "No Books Sold" end from titles
title_id total_sales advance Cost Per Book -------- ----------- ---------- ------------- BU1032 4095 5,000.00 0.82 BU1111 3876 5,000.00 0.78 BU2075 18722 10,125.00 1.85 BU7832 4095 5,000.00 0.82 MC2222 2032 0.00 No Books Sold MC3021 22246 15,000.00 1.48 MC3026 NULL NULL No Books Sold . . . TC3218 375 7,000.00 0.05 TC4203 15096 4,000.00 3.77 TC7777 4095 8,000.00 0.51 (18 rows affected)
The division by zero for title_id MC2222 no longer prevents the query from running. Also, the null values for MC3021 do not prevent the query from running.
case does not avoid division by 0 errors if the divisor evaluates to a constant expression, because Adaptive Server evaluates constant expressions before executing the case logic. This sometimes causes division by zero. As a work around:
Use nullif(). For example:
(x/nullif(@foo.0)0
Include column values so that they cancel each other, forcing Adaptive Server to evaluate the expression for each row. For example:
(x/(@foo + (col1 - col1))
