2009年5月26日 星期二

Msg 3623, Level 16 error in SQL Server

今天執行一個 SQL 指令, 產生下列錯誤訊息:
Msg 3623, Level 16, State 1, Procedure sp0_anprcd_growth, Line 123發生網域錯誤。

查了老半天, 原來是數學運算有錯, 天啊. 這是哪一國的錯誤提示?

指令如下:
select a.證券代碼 , a.證券代碼 + ' ' + max(c.證券中文簡稱) as 證券 , POWER(CAST(10 AS FLOAT),SUM(LOG10(1+[報酬率%]))) as [股價漲跌%] , sum(case 日期 when a.EndDate then b.[收盤價(元)] end) as [迄日收盤價(元)] from #anprcd_1 a, view_anprcd b , view_astkind c where a.證券代碼=b.證券代碼 and a.證券代碼=c.證券代碼 and b.日期 >= a.StartDate and b.日期 <= a.EndDate group by a.證券代碼 order by [股價漲跌%] desc

應修正為
POWER(CAST(10 AS FLOAT),SUM(LOG10(1+[報酬率%]/100)))

感謝恩公如下:
http://www.sql-server-performance.com/faq/domain_error_occurred_p1.aspx