Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
cancel
Showing results for 
Search instead for 
Did you mean: 
kapil_kumar8
Explorer
Requirement -

Sometime we have to divide two numbers in Hana Sql or in Hana views. If denominator comes as 0 there is no logic implemented handle 0 in denominator then Hana will throw Divide by 0 error during Query execution. Also this is good practice to handle denominator 0 case whenever we do any division in our logic.

Solution -

Below are the some options we can use to handle Divide by 0 error in our Hana Sql script or in Hana views.
DO BEGIN
DECLARE Numerator DECIMAL (38, 16);
DECLARE Denominator DECIMAL (38, 16);

Numerator = 100;
Denominator = 0;

-- Divide By 0 Error
--SELECT (Numerator / Denominator) AS "Result" FROM DUMMY;

-- Option 1 Using Case
SELECT CASE WHEN Denominator = 0 THEN 0 ELSE (Numerator / Denominator)
END AS "Using_Case" FROM DUMMY;

-- Option 2 Using NULLIF
SELECT (Numerator / NULLIF(Denominator, 0)) AS "Using_NullIF" FROM DUMMY;

-- Option 3 Using IF..THEN..ELSE
IF Denominator = 0 THEN
SELECT 0 AS "Using_IF" FROM DUMMY;
ELSE
SELECT (Numerator / Denominator) AS "Using_IF" FROM DUMMY;
END IF;

-- Option 4 Using NDIV0 Function
SELECT NDIV0 (Numerator, Denominator) AS "Using_NDIV0" FROM DUMMY;

END;

Implementation of all above options in Graphical view -

Create two calculated columns of type decimal as, Numerator = 100 & Denominator = 6

Function NDIV0 -


Case Statement -



IF.. Condition -


Function NULLIF -


Result with Denominator having Non 0 value -


Result with Denominator having 0 value (No Divide By Zero Error) -

Numerator = 100 & Denominator = 0


Please share if there are other options available.


Thanks & Happy Learning!
Labels in this area