HANA Calculation View - Division by Zero Error

A short post on the fix for the Division by Zero error due to expressions in HANA calculation views

In SQL, a division by zero error occurs when an expression attempts to divide a number by zero, leading to execution failures. In HANA, this can disrupt calculations within your views, especially when working with dynamic datasets where divisor values may be unpredictable.

The Calculated column is a feature in the calculation view that helps modelers insert information based on the other columns from the data sources.

Recently, I had to troubleshoot an issue with HANA View which was throwing an error on access - division by zero undefined: SQL Error. 

The perennial challenge to supporting AMS team consultants is the argument from the stakeholders - "It worked before - Why is it not working now". I struggle to argue coz the opinion is not based on facts but an emotional experience with the system or software. Instead, I try to find the data points and probably share the path to resolution. 

In my case, the issue with the canulated column. On inspection, the issue was due to a calculated column with the expression: Value/Total Value * 100. Earlier datasets never had a total value of zero.

The search for the fix for "Division by zero" error was to use NULLIF. NULLIF converts the zero to NULL which will eliminate the issue of divide by zero. 

HANA Expression editor does not facilitate NULLIF function. A forceful insertion of NULLIF in the expression resulted a Build error. 

After some attempts, I did arrive at a expression which eliminated the error. 

The expression: if("Total_Value",("Value"/ "Total_Value")*100, 0) - This expression evaluates the TOTAL VALUE column. If the column value is 0, the calculation is not performed.

I find this expression mathematically simpler and less intensive on the server. 

I propose to use this logic in any calculation involving division to eliminate the possibility of Error due to Zero in the denominator.