Failure 2619 Division by zero in an expression involving MKTRSK_SRC_PRODUCT_SCHD_EMI.CTR_INSTAL

General
New Member

Failure 2619 Division by zero in an expression involving MKTRSK_SRC_PRODUCT_SCHD_EMI.CTR_INSTAL

 Hi All,

 

We are trying to run below query after migrating Teradata from 14.01 to 15 and are getting error:

 

Query:

 

UPDATE L_DLY1_CAAS_BOTDB.MKTRSK_SRC_PRODUCT_SCHD_EMI
SET RAT_INT_CURRENT = CAST((((((1+AMT_INSTAL/AMT_PRINC_REPAY)**(1/CAST((LOG(1+1/CAST(CTR_INSTAL AS DECIMAL(38,15))) /
LOG(2)) AS DECIMAL(38,15))))-1)**CAST((LOG(1+1/CAST(CTR_INSTAL AS DECIMAL(38,15))) /
LOG(2)) AS DECIMAL(38,15)))-1) AS DECIMAL(38,15)) *100
WHERE RAT_INT_CURRENT IS NULL
AND AMT_INSTAL <> 0
AND CTR_INSTAL <> 0
AND AMT_PRINC_REPAY <> 0
AND AMT_INSTAL < AMT_PRINC_REPAY
AND COUNTRY_ID='BW'
AND CAST((LOG(1+1/CAST(CTR_INSTAL AS DECIMAL(38,15))) / LOG(2)) AS DECIMAL(38,15)) <> 0
;

 

Error:  *** Failure 2619 Division by zero in an expression involving MKTRSK_SRC_PRODUCT_SCHD_EMI.CTR_INSTAL.

 

This query was running fine in Teradata 14. Any clue as if why it is happening in TD15 and what is the potential solution for this?

 

Regards,

Abhay

1 REPLY
Teradata Employee

Re: Failure 2619 Division by zero in an expression involving MKTRSK_SRC_PRODUCT_SCHD_EMI.CTR_INSTAL

Division by CTR_INSTAL is occurring in the WHERE clause. SQL does not define an execution order for expressions in the where clause. The best way to think of it is to assume that all expressions will be evaluated. The optimizer is free to order them as it sees fit. Thus it is possible that the division expression could be executed before the <>0 expression. And the order can change as the optimizer is changed/enhanced.

To bullet proof this query the expression needs to be enhanced to deal with the case of CTR_INSTAL being zero. E.g. CASE(WHEN CTR_INSTAL<>0 THEN CTR_INSTAL ELSE NULL)