我有这个错误信息:
Msg 8134,级别16,状态1,第1行,除以零错误。
写SQL代码的最好方法是什么,这样我就再也不会看到这个错误消息了?
我可以做以下任何一件事:
添加where子句,使除数永远不为零
Or
我可以添加一个case语句,这样就可以对0进行特殊处理。
使用NULLIF子句是最好的方法吗?
有没有更好的办法,或者如何实施?
我有这个错误信息:
Msg 8134,级别16,状态1,第1行,除以零错误。
写SQL代码的最好方法是什么,这样我就再也不会看到这个错误消息了?
我可以做以下任何一件事:
添加where子句,使除数永远不为零
Or
我可以添加一个case语句,这样就可以对0进行特殊处理。
使用NULLIF子句是最好的方法吗?
有没有更好的办法,或者如何实施?
当前回答
使用NULLIF(exp,0),但以这种方式- NULLIF(ISNULL(exp,0),0)
如果exp为空,NULLIF(exp,0)将中断,但NULLIF(ISNULL(exp,0),0)将不会中断
其他回答
为了避免“除零”错误,我们编写了这样的程序:
Select Case when divisor=0 then null
Else dividend / divisor
End ,,,
但这里有一种更好的方式:
Select dividend / NULLIF(divisor, 0) ...
现在唯一的问题是记住NullIf位,如果我使用“/”键。
你也可以在查询的开头这样做:
SET ARITHABORT OFF
SET ANSI_WARNINGS OFF
如果你有100/0,它会返回NULL。我只做了简单的查询,所以我不知道它会如何影响更长的/复杂的查询。
在这种情况下,你可以除以0。商业规则是计算库存周转,你用一段时间内的销售成本,按年计算。在你得到年化数字之后,你要除以该时期的平均库存。
I'm looking at calculating the number of inventory turns that occur in a three month period. I have calculated that I have Cost of Goods sold during the three month period of $1,000. The annual rate of sales is $4,000 ($1,000/3)*12. The beginning inventory is 0. The ending inventory is 0. My average inventory is now 0. I have sales of $4000 per year, and no inventory. This yields an infinite number of turns. This means that all my inventory is being converted and purchased by customers.
这是关于如何计算库存周转的业务规则。
There is no magic global setting 'turn division by 0 exceptions off'. The operation has to to throw, since the mathematical meaning of x/0 is different from the NULL meaning, so it cannot return NULL. I assume you are taking care of the obvious and your queries have conditions that should eliminate the records with the 0 divisor and never evaluate the division. The usual 'gotcha' is than most developers expect SQL to behave like procedural languages and offer logical operator short-circuit, but it does NOT. I recommend you read this article: http://www.sqlmag.com/Articles/ArticleID/9148/pg/2/2.html
对于更新sql:
update Table1 set Col1 = Col2 / ISNULL(NULLIF(Col3,0),1)