線上訂房服務-台灣趴趴狗聯合訂房中心
發文 回覆 瀏覽次數:2476
推到 Plurk!
推到 Facebook!

SQL update 大量資料時出現"發現除以零的錯誤"

答題得分者是:GrandRURU
老大仔
尊榮會員


發表:77
回覆:835
積分:1082
註冊:2006-07-06

發送簡訊給我
#1 引用回覆 回覆 發表時間:2013-05-07 09:51:22 IP:210.61.xxx.xxx 未訂閱
各位前輩好~
小弟我這兩天在MSSQL資料庫遇到一件詭異的事
以下SQL在之前跑整批更新都沒問題
但當要跑4月份時卻一直過不了
會一直出現"發現除以零的錯誤"
查了下資料庫資料
確定是沒問題的!!(沒有分母為0的資料)
把以下SQL替換成Select查詢時沒問題
只有在Update 時會這樣...QQ

我記得以前在這類語法時也沒問題
但就在昨天才開始這樣
到現在一直解決不了
有點困擾...

請問有前輩們遇過這類的怪問題嗎??


[code sql]
Update AAA
Set S = (IsNull(A, 0) IsNull(B, 0)) / (IsNull(A_QTY, 0) IsNull(B_QTY, 0) IsNull(C_QTY, 0) - IsNull(D_QTY, 0))
Where YYMM='201304'
and ((IsNull(A_QTY, 0) IsNull(B_QTY, 0) IsNull(C_QTY, 0) - IsNull(D_QTY, 0)) <> 0)


Select *
From AAA
Where YYMM='201304'
and ((IsNull(A_QTY, 0) IsNull(B_QTY, 0) IsNull(C_QTY, 0) - IsNull(D_QTY, 0)) <> 0)
[/code]



資料庫是使用MSSQL 2005(層級80)
ps:因某些因素 尚不能改為90...

編輯記錄
老大仔 重新編輯於 2013-05-07 09:52:30, 註解 無‧
老大仔 重新編輯於 2013-05-07 09:57:27, 註解 無‧
老大仔 重新編輯於 2013-05-07 09:57:47, 註解 無‧
herbert2
尊榮會員


發表:58
回覆:632
積分:878
註冊:2004-04-16

發送簡訊給我
#2 引用回覆 回覆 發表時間:2013-05-07 10:54:14 IP:202.39.xxx.xxx 訂閱
要不要試一下是否有分母=0的 Row ?

Select *
From AAA
Where YYMM='201304'
and ((IsNull(A_QTY, 0) IsNull(B_QTY, 0) IsNull(C_QTY, 0) - IsNull(D_QTY, 0)) = 0)
GrandRURU
站務副站長


發表:234
回覆:1651
積分:1742
註冊:2005-06-21

發送簡訊給我
#3 引用回覆 回覆 發表時間:2013-05-07 11:25:47 IP:59.120.xxx.xxx 訂閱
採繞路的方式測看看吧


[code sql]
SELECT * INTO #TempAAA FROM AAA
Where YYMM='201304'
and ((IsNull(A_QTY, 0) IsNull(B_QTY, 0) IsNull(C_QTY, 0) - IsNull(D_QTY, 0)) <> 0)

update u
set S = (A B)/(A_QTY B_QTY C_QTY-D_QTY)
from AAA u
inner join #TempAAA q on
u.ID = q.ID
WHERE u.YYMM='201304'
[/code]


老大仔
尊榮會員


發表:77
回覆:835
積分:1082
註冊:2006-07-06

發送簡訊給我
#4 引用回覆 回覆 發表時間:2013-05-07 14:44:40 IP:210.61.xxx.xxx 未訂閱
herbert2 大大~
確定沒有分母為0的資料QQ


===================引 用 herbert2 文 章===================
要不要試一下是否有分母=0的 Row ?

Select *
From AAA
Where YYMM='201304'
and ((IsNull(A_QTY, 0) IsNull(B_QTY, 0) IsNull(C_QTY, 0) - IsNull(D_QTY, 0)) = 0)
老大仔
尊榮會員


發表:77
回覆:835
積分:1082
註冊:2006-07-06

發送簡訊給我
#5 引用回覆 回覆 發表時間:2013-05-07 14:50:06 IP:210.61.xxx.xxx 未訂閱
GrandRURU 大大~
謝謝您提供的方法~
目前還沒用這方法測試
也不確定是否可採此方法改
因為這牽涉到一些成本計算的東西
程式現階段也不是我維護的
所以不太敢亂動程式

但我會向上級提出此方法

目前先將該程式放到另一台主機測試中
還在等待結果ing



===================引 用 GrandRURU 文 章===================
採繞路的方式測看看吧


[code sql]
SELECT * INTO #TempAAA FROM AAA
Where YYMM='201304'
and ((IsNull(A_QTY, 0) IsNull(B_QTY, 0) IsNull(C_QTY, 0) - IsNull(D_QTY, 0)) <> 0)

update u
set S = (A B)/(A_QTY B_QTY C_QTY-D_QTY)
from AAA u
inner join #TempAAA q on
u.ID = q.ID
WHERE u.YYMM='201304'
[/code]


carstyc
資深會員


發表:16
回覆:254
積分:329
註冊:2003-07-18

發送簡訊給我
#6 引用回覆 回覆 發表時間:2013-05-07 21:47:19 IP:219.84.xxx.xxx 訂閱
為何不直接 SELECT 結果出來看會不會有除以0的問題呢?



Select (IsNull(A, 0) IsNull(B, 0)) / (IsNull(A_QTY, 0) IsNull(B_QTY, 0) IsNull(C_QTY, 0) - IsNull(D_QTY, 0))
From AAA
Where YYMM='201304'
and ((IsNull(A_QTY, 0) IsNull(B_QTY, 0) IsNull(C_QTY, 0) - IsNull(D_QTY, 0)) <> 0)



===================引 用 老大仔 文 章===================
各位前輩好~
小弟我這兩天在MSSQL資料庫遇到一件詭異的事
以下SQL在之前跑整批更新都沒問題
但當要跑4月份時卻一直過不了
會一直出現"發現除以零的錯誤"
查了下資料庫資料
確定是沒問題的!!(沒有分母為0的資料)
把以下SQL替換成Select查詢時沒問題
只有在Update 時會這樣...QQ

我記得以前在這類語法時也沒問題
但就在昨天才開始這樣
到現在一直解決不了
有點困擾...

請問有前輩們遇過這類的怪問題嗎??


[code sql]
Update AAA
Set S = (IsNull(A, 0) IsNull(B, 0)) / (IsNull(A_QTY, 0) IsNull(B_QTY, 0) IsNull(C_QTY, 0) - IsNull(D_QTY, 0))
Where YYMM='201304'
and ((IsNull(A_QTY, 0) IsNull(B_QTY, 0) IsNull(C_QTY, 0) - IsNull(D_QTY, 0)) <> 0)


Select *
From AAA
Where YYMM='201304'
and ((IsNull(A_QTY, 0) IsNull(B_QTY, 0) IsNull(C_QTY, 0) - IsNull(D_QTY, 0)) <> 0)
[/code]



資料庫是使用MSSQL 2005(層級80)
ps:因某些因素 尚不能改為90...

編輯記錄
carstyc 重新編輯於 2013-05-07 21:53:04, 註解 無‧
aftcast
站務副站長


發表:81
回覆:1482
積分:1762
註冊:2002-11-21

發送簡訊給我
#7 引用回覆 回覆 發表時間:2013-05-07 22:03:18 IP:114.42.xxx.xxx 訂閱
 我大膽的猜測: 你的那些欄位,有些是「計算欄位」,即該欄的值是某些欄位的(除法結果)。而不是欄位本身是null 。

不然很可能是見鬼! :p
------



蕭沖
--All ideas are worthless unless implemented--

C++ Builder Delphi Taiwan G+ 社群
http://bit.ly/cbtaiwan
aftcast
站務副站長


發表:81
回覆:1482
積分:1762
註冊:2002-11-21

發送簡訊給我
#8 引用回覆 回覆 發表時間:2013-05-07 22:14:12 IP:114.42.xxx.xxx 訂閱
補充一下,
有查過 select * from ... where A_QTY = 0 and… 過嗎?
或是在update 前後加入 begin tran 最後rollback,看影響幾列,或是跳錯。 這是很重要的debug sql的方法之一!

begin trans

update.........
(B, 0)) / ((A_QTY, 1)

rollback trans

===================引 用 aftcast 文 章===================
我大膽的猜測: 你的那些欄位,有些是「計算欄位」,即該欄的值是某些欄位的(除法結果)。而不是欄位本身是null 。

不然很可能是見鬼! :p
------



蕭沖
--All ideas are worthless unless implemented--

C++ Builder Delphi Taiwan G+ 社群
http://bit.ly/cbtaiwan
老大仔
尊榮會員


發表:77
回覆:835
積分:1082
註冊:2006-07-06

發送簡訊給我
#9 引用回覆 回覆 發表時間:2013-05-08 07:33:44 IP:210.61.xxx.xxx 未訂閱
carstyc 大大:
有用這方法檢查過
查詢出來確定有資料而且沒有除以零的錯誤

===================引 用 carstyc 文 章===================
為何不直接 SELECT 結果出來看會不會有除以0的問題呢?



Select (IsNull(A, 0) IsNull(B, 0)) / (IsNull(A_QTY, 0) IsNull(B_QTY, 0) IsNull(C_QTY, 0) - IsNull(D_QTY, 0))
From AAA
Where YYMM='201304'
and ((IsNull(A_QTY, 0) IsNull(B_QTY, 0) IsNull(C_QTY, 0) - IsNull(D_QTY, 0)) <> 0)


老大仔
尊榮會員


發表:77
回覆:835
積分:1082
註冊:2006-07-06

發送簡訊給我
#10 引用回覆 回覆 發表時間:2013-05-08 07:42:26 IP:210.61.xxx.xxx 未訂閱
aftcast 大大:
目前除了YYMM是Varchar
剩下的欄位確定都是 decimal(15, 4)、decimal(15, 6)
沒有計算的欄位

謝謝您提醒的Debug方法
我都快忘了還能這樣做...

另外
A_QTY、B_QTY..幾個欄位可能是0
但就是因為怕分母為0
所以有在where條件上故意排除掉

假如再不行...
我想準備來買箱乖乖回來放了...





===================引 用 aftcast 文 章===================
補充一下,
有查過 select * from ... where A_QTY = 0 and… 過嗎?
或是在update 前後加入 begin tran 最後rollback,看影響幾列,或是跳錯。 這是很重要的debug sql的方法之一!

begin trans

update.........
(B, 0)) / ((A_QTY, 1)

rollback trans

===================引 用 aftcast 文 章===================
我大膽的猜測: 你的那些欄位,有些是「計算欄位」,即該欄的值是某些欄位的(除法結果)。而不是欄位本身是null 。

不然很可能是見鬼! :p
老大仔
尊榮會員


發表:77
回覆:835
積分:1082
註冊:2006-07-06

發送簡訊給我
#11 引用回覆 回覆 發表時間:2013-05-08 07:50:00 IP:210.61.xxx.xxx 未訂閱

目前用此方法測
還是會有錯誤訊息
而且也沒有跑出影響幾列...QQ

[code sql]
Begin Tran
Update AAA
Set S = (IsNull(A, 0) IsNull(B, 0)) / (IsNull(A_QTY, 0) IsNull(B_QTY, 0) IsNull(C_QTY, 0) - IsNull(D_QTY, 0))
Where YYMM='201304'
and ((IsNull(A_QTY, 0) IsNull(B_QTY, 0) IsNull(C_QTY, 0) - IsNull(D_QTY, 0)) <> 0)
if @@Error <> 0
begin
RollBack Tran
Return
end
Commit Tran

[/code]





===================引 用 aftcast 文 章===================
補充一下,
有查過 select * from ... where A_QTY = 0 and… 過嗎?
或是在update 前後加入 begin tran 最後rollback,看影響幾列,或是跳錯。 這是很重要的debug sql的方法之一!

begin trans

update.........
(B, 0)) / ((A_QTY, 1)

rollback trans


編輯記錄
老大仔 重新編輯於 2013-05-08 07:52:15, 註解 無‧
aftcast
站務副站長


發表:81
回覆:1482
積分:1762
註冊:2002-11-21

發送簡訊給我
#12 引用回覆 回覆 發表時間:2013-05-08 10:29:41 IP:114.32.xxx.xxx 訂閱
嗯,昨半夜沒認真的看你的where句字。誤判了!

我想,最後的可能性是……

你要把它放到 query analyzer ( sql2000叫這個,新版我不知它是什麼,很久沒用)。看你的查詢被怎樣安排。就好像編譯器一樣,寫是那樣,但因為優化或是什麼原因,查詢的一整個過程(分段的過程),可能並不如我們想的…

該分析會把先做什麼,然後再什麼…分出來。也許可以從那裡看出一些問題是卡哪一關。

還是看還有誰快來幫忙喔~~ (我手上目前沒有裝mssql… :p )

===================引 用 老大仔 文 章===================

目前用此方法測
還是會有錯誤訊息
而且也沒有跑出影響幾列...QQ

[code sql]
Begin Tran
Update AAA
Set S = (IsNull(A, 0) IsNull(B, 0)) / (IsNull(A_QTY, 0) IsNull(B_QTY, 0) IsNull(C_QTY, 0) - IsNull(D_QTY, 0))
Where YYMM='201304'
and ((IsNull(A_QTY, 0) IsNull(B_QTY, 0) IsNull(C_QTY, 0) - IsNull(D_QTY, 0)) <> 0)
if @@Error <> 0
begin
RollBack Tran
Return
end
Commit Tran

[/code]





===================引 用 aftcast 文 章===================
補充一下,
有查過 select * from ... where A_QTY = 0 and… 過嗎?
或是在update 前後加入 begin tran 最後rollback,看影響幾列,或是跳錯。 這是很重要的debug sql的方法之一!

begin trans

update.........
(B, 0)) / ((A_QTY, 1)

rollback trans


------



蕭沖
--All ideas are worthless unless implemented--

C++ Builder Delphi Taiwan G+ 社群
http://bit.ly/cbtaiwan
老大仔
尊榮會員


發表:77
回覆:835
積分:1082
註冊:2006-07-06

發送簡訊給我
#13 引用回覆 回覆 發表時間:2013-05-08 12:35:21 IP:210.61.xxx.xxx 未訂閱
aftcast 大大~
紅色字段基本上~有看懂~
但...
不知道功能在SQL2005的哪裡~



===================引 用 aftcast 文 章===================
嗯,昨半夜沒認真的看你的where句字。誤判了!

我想,最後的可能性是……

你要把它放到 query analyzer ( sql2000叫這個,新版我不知它是什麼,很久沒用)。看你的查詢被怎樣安排。就好像編譯器一樣,寫是那樣,但因為優化或是什麼原因,查詢的一整個過程(分段的過程),可能並不如我們想的…

該分析會把先做什麼,然後再什麼…分出來。也許可以從那裡看出一些問題是卡哪一關。


還是看還有誰快來幫忙喔~~ (我手上目前沒有裝mssql… :p )


老大仔
尊榮會員


發表:77
回覆:835
積分:1082
註冊:2006-07-06

發送簡訊給我
#14 引用回覆 回覆 發表時間:2013-05-08 12:57:18 IP:210.61.xxx.xxx 未訂閱
各位前輩大家好~
後來試用 GrandRURU 大大的方式
建一新的暫存表:Z
結構與AAA相同
先把要update AAA的資料的條件用Select into到新的暫存表:Z 裡
然後再把原先update的語法改成update Z
(所有欄位、條件不動)
結果~
居然可以pass...

一樣的語法
AAA 過不了
Z可以pass...

哇哩咧...

目前採用以上方法
然後再將AAA drop掉並重新Create Table AAA
再把Z的資料倒回AAA
再做一次Update AAA

然後~






居然成功了...= =

真的是活見鬼了
搞了兩天終於解決...(/‵Д′)/~ ╧╧

但我還是想知道~到底是啥原因...
資料表壞去了?


也謝謝
herbert2GrandRURUcarstycaftcast 前輩們熱心回應&提供解決的方式~
老大仔
尊榮會員


發表:77
回覆:835
積分:1082
註冊:2006-07-06

發送簡訊給我
#15 引用回覆 回覆 發表時間:2013-05-09 07:34:20 IP:210.61.xxx.xxx 未訂閱
另外
將相同資料庫備份、還原至另一台機器上面
SQL原封不動、不作任何修改(前一篇)
重新跑
結果沒問題...

系統時間:2017-10-23 12:11:57
聯絡我們 | Delphi K.Top討論版
本站聲明
1. 本論壇為無營利行為之開放平台,所有文章都是由網友自行張貼,如牽涉到法律糾紛一切與本站無關。
2. 假如網友發表之內容涉及侵權,而損及您的利益,請立即通知版主刪除。
3. 請勿批評中華民國元首及政府或批評各政黨,是藍是綠本站無權干涉,但這裡不是政治性論壇!