The decode function solves the problem that Oracle reports an error “divisor is 0”

When the company’s website was running, it suddenly reported an error and couldn’t open. When I opened it, I found an error: ora-01476: the divisor is 0

A search on the Internet found that many people encountered this problem. The solution is to use the decode function

Decode is a built-in function of Oracle

Explanation of meaning:
decode (condition, value 1, return value 1, value 2, return value 2,… Value n, return value n, default value)

The meaning of this function is as follows:
if condition = value 1 then
return (translation value 1)
elsif condition = value 2 then
return (translation value 2)

elsif condition = value n then
return (translation value n)
return (default)
end if


In short, decode (Exp1, 0, 0, exp2/Exp1) means that when Exp1 is equal to 0, exp2/Exp1 no longer reports an error, but returns 0. When Exp1 is not equal to 0, the result of exp2/Exp1 is returned

In the future, when writing SQL statements with division, try to use the decode function


Similar Posts: