I have a logic where doing some calculation like below is giving error as

ORA-01476: divisor is equal to zero

I have added CASE condition like below

V_EFFEC_PER_APPFAIR := (case when v_YEARMSAFORAPPRENT <> then to_char((POWER(1 (v_YEARMSAFORAPPRENT * 2.5/ 100 / v_YEARMSAFORAPPRENT), v_YEARMSAFORAPPRENT) - 1) * 100, '99990D99' end) as

Please help where I am going wrong.

Full code is below

v_YEARMSAFORAPPRENT := floor((months_between(to_date(V_BILLSTART_DT), to_date(V_FINALSRDATE, 'dd-mm-yy'))) /12);
V_AMTINMONTH := CAST(to_char(LAST_DAY(V_DATEVARIABLE),'dd') AS INT); 
V_EFFEC_PER_APPFAIR := (case when v_YEARMSAFORAPPRENT <> then to_char((POWER(1   
 (v_YEARMSAFORAPPRENT * 2.5/ 100 / v_YEARMSAFORAPPRENT), v_YEARMSAFORAPPRENT) - 1) * 100, 
'99990D99' end) as
 V_APP_FAIR_RENT := (v_FAIR_RENT_AMT * V_EFFEC_PER_APPFAIR) / 100   v_FAIR_RENT_AMT;
V_APP_FAIR_RENT := V_APP_FAIR_RENT / V_AMTINMONTH;
V_APP_FAIR_RENT := ROUND(V_APP_FAIR_RENT * V_NOOFDAYSINMONTH);

CodePudding user response：

V_EFFEC_PER_APPFAIR := (case when v_YEARMSAFORAPPRENT <> then to_char((POWER(1   
 (v_YEARMSAFORAPPRENT * 2.5/ 100 / v_YEARMSAFORAPPRENT), v_YEARMSAFORAPPRENT) - 1) * 100, 
'99990D99' end) as

Has multiple errors.

• case when v_YEARMSAFORAPPRENT <> then is invalid syntax as there is nothing on the right-hand side of the <> operator.
• The statement ends with as and not a ; statement terminator.
• In v_YEARMSAFORAPPRENT * 2.5/ 100 / v_YEARMSAFORAPPRENT you can cancel out the v_YEARMSAFORAPPRENT terms and simplify the entire statement to just 0.025

You have other errors including:

• Using TO_DATE on V_BILLSTART_DT and V_FINALSRDATE which, although you have not given the data type, are presumably DATE data types and you should NEVER use TO_DATE on values that are already dates.
• months_between(date1, date2) subtracts date2 from date1 and not the other way round. I'm assuming you want to subtract the start date from the final date and end up with a positive value; not subtract the final date from the start date and end up with a negative value.

You probably want something like:

DECLARE
  V_BILLSTART_DT DATE := DATE '2022-01-01';
  V_FINALSRDATE  DATE := DATE '2022-12-31';
  v_YEARMSAFORAPPRENT NUMBER;
  V_EFFEC_PER_APPFAIR VARCHAR2(10);
BEGIN
  v_YEARMSAFORAPPRENT := floor(months_between(V_FINALSRDATE, V_BILLSTART_DT)/12);
  V_EFFEC_PER_APPFAIR  := to_char(
                            (POWER(1.025, v_YEARMSAFORAPPRENT) - 1) * 100, 
                            'fm99990D00'
                          );
  DBMS_OUTPUT.PUT_LINE(V_EFFEC_PER_APPFAIR);
END;
/

Note: I've ignored the other lines as they did not appear to be relevant to your stated error message; there may be more errors in those ignored lines but you'll have to debug them yourself.