ORA-01476: "divisor is equal to zero" for SQL query (Oracle 10g)

Question

I'm creating an SQL Query under oracle 10g, the result should give me something like that :

----------------------------------------------------------------------------------
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:

I tried DECODE function but i stil have the same error, it's the same for CASE

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

Thanks and regards

Answer 1

It all depends on whether you want to calculate a value if it would result in an infinite value or not. You can either ignore these particular instances and calculate on the remainder as Gordon's answer suggests with:

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

Alternatively, if want to ignore them you can use NULLIF() to change the value to NULL and not calculate anything:

value3 / nullif(value2, 0) * 100

I do not understand your contention that this being a calculated column causes an issue. If it's a virtual column then your table would never have created, as specified in the documentation a virtual column cannot refer to another by name.

If it's not a virtual column then you can do this in a select statement as normal.