Handle Divide By 0 Error in Hana Sql or Hana View

[kapil_kumar8]

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!