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.
Below are the some options we can use to handle Divide by 0 error in our Hana Sql script or in Hana graphical 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 / NULLIF(Denominator, 0)) AS "Using_IF" FROM DUMMY; END IF; -- Option 4 Using NDIV0 Function SELECT NDIV0 (Numerator, Denominator) AS "Using_NDIV0" FROM DUMMY; END;
We can use NDIV0 function in Calculated Columns in Hana view.
Calculated Column –
Please share if there is any other options are also available.
Thanks & Happy Learning!