Famous ‘divide by zero’ error!

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! 😀

Good Day!

You must be Logged on to comment or reply to a post.
• Hi,

I think this is a wrong suggestion. Please read the documentation as per which we cannot use if function to check devide by zero errors. You have to use a case function.

Functions used in Expressions – SAP HANA Modeling Guide – SAP Library

“return arg2 if intarg is considered true (not equal to zero), else return arg3. Currently, no shortcut evaluation is implemented, meaning that both arg2 and arg3 are evaluated in any case. This means you cannot use if to avoid a divide by zero error which has the side effect of terminating expression evaluation when it occurs.”

Ramana

• Former Member Post author

Hi Ramana,

Thanks for your comment and for highlighting this.

I will modify the suggestion accordingly.

But have you tried this approach and is there some real problem which will occur if we use the IF function?

I have currently tested this using the IF function, and it seems to be working fine.

-Jordan

• much better option: `Select dividend / nullif(divisor, 0)`

• Thanks Jordan for this blog! This helped me a lot.

With if(), I was struggling and this blog came as a saviour.