Divide by Zero error message when I'm not dividing by Zero. 2

[shearbec]

Hi All,

I have a condition that is as such:

if (A<>0) then ((B-A)/A) else Null.

I get the error message stating this:

Error number -51:

DMS-E-MATHEXCEPTION, An arithmetic exception was detected.
EXPENG-E-ZERO_DIVR, <Floating point> divide/mod by zero is invalid.

However, if I change the formula to be &quot;if (A<>0) then (1) else Null&quot; it will work fine. Also, if I change the condition to be &quot;if (A<>0 and B<>0) then ((B-A)/A) else null and I get the same thing.

Any help would be much appreciated. I should also mention that A and B are group totals.

Thanks...Shearbec

 

[Shek]

try if (A is not missing or A<> 0 ) then ((B-A)/A) else Null.

 

[griffindm]

This is a well known issue with Impromptu. The problem stems from what I would call 'optimistic' processing. Impromptu uses the setting under Reports | Query | Client Server to assist it in determing WHERE to execute portions of the requested data operations. Depending on your database type, it may choose to send your division result to the database BEFORE determing the result of your If-Then-Else statement. This is a particular problem on Oracle. The solution is to place the if-then-else into the denomenator to force Impromptu to do the division locally, As in:

column1 / If (column2 <> 0) then (column2) else NULL.

Note the use of NULL. This approach worked fine until Impromptu 6, when Cognos added the 'help' of automatically applying a DECODE function whenever an If-Then-Else function is used. This 'improvement' has been a pain in the butt, and most serious report developers have long since disabled it. To do this modify the Impromptu INI with:

[Query Options]
use automatic ORACLE Decode=0