ORA-01476 divisor is equal to zero for SQL query Oracle 10g

Question

I'm creating a SQL query with Oracle 10g, and the output should look like this:

----------------------------------------------------------------------------------
TEXT         VALUE1        VALUE2          VALUE3               VALUE4
---------------------------------------------------------------------------------
TEXT1        8795           5684        value1-value2          value3/value2*100
TEXT2        235             568            ...                   ...
TEXT3        125             23             ...                   ...
TEXT4        789             58             ...                   ...
TEXTN         0               0             ...                   ...

When I try to calculate VALUE4 column I get this error :

ORA-01476: le diviseur est égal à zéro
01476. 00000 -  "divisor is equal to zero"
*Cause:    
*Action:

The same error persists despite my attempts to use the DECODE function and the CASE function.

NB: The calculated columns VALUE1, VALUE2, VALUE3, and VALUE4 are: VALUE1 = sum(col1)+sum(col2); and similarly for the other VALUE2 column.

Can someone please help me with this?

Answer 1

It all relies on whether you want to determine whether or not calculating a value would produce an endless value. You can either disregard these specific instances and compute the remainder as Gordon advises in his response with:

case when value2 <> 0 then value3 / value2 * 100 end

Alternatively, you can use NULLIF() to set the value to NULL and not do any calculations if you want to disregard them:

value3 / nullif(value2, 0) * 100

I don't see why you claim that this calculated column's existence creates a problem. Since virtual columns cannot refer to other virtual columns by name, your table would not have existed if it were a virtual column. You can perform this in a select statement as usual if it's not a virtual column.

I hope this helps you.