Error Handling on 01428

[PJFry]

I am using the LN function on a data set and it is returning negative values. Is there a way to bypass that error or just have a valid number substituted?

Is there any more data needed to answer this question?

Thanks,
PJ

 

[SantaMufasa]

PJ,

As you probably know, the LN function requires a positive argument (which, in your case, is causing the ORA-01428 error). So, if a non-positive argument is your problem, you can deal with it in this fashion:

...decode(sign(<column-name>),-1,null,0,null,1,ln(<column-name>)...

...which means, if the current content of <column-name> is:
negative, then return null
0, then return null
positive, then return the natural logarithm of <column-name>.

Let us know if this resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[PJFry]

Thanks for the response.

I am still getting the same error. Where you had listed <column-name> is where I am running a function based on three other fields. In this case, none of the columns I am looking at have negative values, the calculation I am using produces those values.

What I am doing is calculating the effective number of payments remaining on a loan. Here is the formula:

ROUND ( LN ( (1/(1-((Loan_Bal_Amt*(Int_Rt/12)))/Pmt_Amt)))/ LN ( 1+(Int_Rt/12) ),2)

The logic you listed above seems right, I just need to fit it to a calculation rather than a column name.

Thanks!

 

[SantaMufasa]

Since the only way that you should get this error on an "LN" function is if the value of its argument is not positive, I propose that you try:

SECLET count(*) from <table_name>
WHERE Loan_Bal_Amt*(Int_Rt/12)/Pmt_Amt < 1;

If the result > 0, then you know you have problems. You should then display the rows "WHERE Loan_Bal_Amt*(Int_Rt/12)/Pmt_Amt < 1" or adjust your calculation.

Let us know,



Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]