oracle 除数等于零
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13784602/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
divisor is equal to zero
提问by user1873093
i have the following data but in query i got the zero problem in division by zero. so i want the output as given below in two forms.
我有以下数据，但在查询中我得到了除以零的零问题。所以我想要以下两种形式的输出。
drop table t;
create table t (id number(9), val1 number(9), val2 number(9));
insert into t values (1,23,2);
insert into t values (2,0,4);
insert into t values (3,7,4);
insert into t values (4,0,3);
insert into t values (5,4,6);
select sqrt(val2*val1)/val1 from t ;
从 t 中选择 sqrt(val2*val1)/val1 ；
SQL> / ERROR: ORA-01476: divisor is equal to zero
SQL> / 错误：ORA-01476：除数等于零
expected reults
预期结果
in two forms first query like this
以两种形式首先查询像这样
ID SQRT(VAL2*VAL1)/VAL1
---------- --------------------
1 .294883912
2 0
3 .755928946
4 0
5 1.22474487
second query like this
像这样的第二个查询
ID SQRT(VAL2*VAL1)/VAL1
---------- --------------------
1 .294883912
3 .755928946
5 1.22474487
回答by kcsarath
You can use
您可以使用
select sqrt(val2*val1)/val1 from t where val1 != 0;
select sqrt(val2*val1)/val1 from t where val1 != 0;
That will avoid the rows where val1 is 0 and so will not cause the "divide by 0" error. This should give you the second format.
这将避免 val1 为 0 的行，因此不会导致“除以 0”错误。这应该为您提供第二种格式。
For the first format where you want to show the 0 in the output you can use a case statement in the where clause.
对于要在输出中显示 0 的第一种格式，您可以在 where 子句中使用 case 语句。
select id,
case
when val1 = 0 then 0
when val1 != 0 then sqrt(val2*val1)/val1
end
from t
回答by Edmon
Consider using a DECODE and NVL. In DECODE (if/then/else) specify the logic with or without zero. If you want to use some other value instead, use NVL.
考虑使用解码器和 NVL。在 DECODE (if/then/else) 中指定带或不带零的逻辑。如果您想改用其他值，请使用 NVL。
Decode example:
解码示例：
SELECT DECODE(val1, 0, NULL, sqrt(val2*val1)/val1) from t;
Hope this helps.
希望这可以帮助。
回答by Moiz
In your Data insert into t values (2,0,4);val1 is 0 so it will not allow this to Divide with 0. so either you need to check first weather val1 is 0 or not then perform this.
在您的数据中插入into t values (2,0,4);val1 为 0，因此它不允许将其除以 0。因此，您需要先检查天气 val1 是否为 0 或不执行此操作。
