Famous 'divide by zero' error!

[former_member202684]

We all know that BI applications generally contain many calculations.

As developers we encounter multiple errors while handling such business calculations. One of such frequently encountered and rather silly or irritating error is the 'Divide by zero' error. :!:

In different technologies we have different methods using which we can handle or suppress the 'Divide by zero' error and display BLANK or zero in the result.

For SAP BI/BW, In the BEx Query Designer we have a specific data function which is designed to handle this scenario when division by 0 occurs.

NDIV0 (<Expression>) - results in 0 if the <expression> causes a division by zero. Otherwise we get the result of the <expression> as the output.

Example: NDIV0 (100/5) would give us 20. NDIV0 (100/0) would give us 0.

The same situation can also be handled by using NOERR (<Expression>) function which suppresses the error and would again give us 0 in case of an erroneous or undefined calculation.

I recently used a division expression in a Calculated Column in my Calculation View, and I did not bother to handle the division by zero. So here goes, I immediately get a 'Divide by zero' error.

I was browsing through SCN to see if this has been encountered and handled by anybody in SAP HANA, but unfortunately I couldn't find any post/blog/document for such an encounter in HANA.

Hence this blog for my fellow developers who possibly encountered the same issue or are going to encounter this issue showing a simple solution to handle this error.

Though I know its a very simple way and others might have the same idea, I thought I could just put across my approach.

In order to avoid the 'divide by zero' error in an expression like <numerator / denominator>, we just add a condition to check if the denominator is zero, then result = 0, else the calculation.

Example: if my calculation is variable1/variable2, I would use the formula as below:

ℹ "IF ( variable2 = 0, 0, variable1 / variable2 )" - this would simple give the output as zero in case of division by zero, else perform the calculation.

Edit:

I was aware that we could also use the CASE function to achieve the same functionility, but just realized that SAP suggests not to use the if function to avoid the divide by zero error. check the link.

We would have to use the CASE function. case(B, 0, 0, A/B).

Hope this helps somebody! :grin:

Good Day!