case and Division by Zero

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 SAP ASE 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 SAP ASE to evaluate the expression for each row. For example:
```
(x/(@foo + (col1 - col1))
```