2013年8月19日 星期一

如何避免除0錯誤-NULLIF函數之應用

在報表常需計算相關比率,對於除法運算中分母是否為零之檢查與處理影響甚鉅,若無適當處理將發生除零錯誤,在此將介紹在SQL中如何避免除零錯誤,除可應用CASE WHEN檢查分母(denominator)是否為零,若為零則傳回NULL,反之則傳回計算結果;另外,也可利用NULLIF函數即可處理。SQL SERVERORACLE均有支援此指令,SQL如下所示。
SQL SERVER
ORACLE
SELECT 100.0* 1/ NULLIF(Val, 0) "Ratio(%)"
, '1/'+ CAST(VAL AS VARCHAR)
+ ' (' + TestCase +')' TestCase
FROM
(
SELECT 0 Val, '分母為零' TestCase
UNION ALL
SELECT 2 Val, '分母不為零' TestCase
) A

SELECT  1/ NULLIF(Val, 0) *100 "Ratio(%)"
, '1/' || Val ||' (' || TestCase || ')' TestCase
FROM
(
SELECT 0 Val, '分母為零' TestCase
FROM DUAL
UNION ALL
SELECT 2 Val, '分母不為零' TestCase
FROM DUAL
)


前述執行結果如下所示,利用任何值除以NULL運結果為NULL的特性透過NULLIF函數將分母為零的資料轉換為NULL值,即可避免除零錯誤
Ratio(%)
TestCase

1/0 (分母為零)
50
1/2 (分母不為零)
 *註: 任何資料與NULL進行運算,只有少數狀況不為NULL。

以下將介紹NULLIF的使用。NULLIF語法如下:
NULLIF ( expr1 , expr2 )
若兩個運算式不相等,即expr1<>expr2,則NULLIF傳回expr1。若運算式相等(expr1=expr2),將傳回NULL值。前述利用CASE WHEN是判斷分母不為零時進行運算。另外,可將NULLIF函數轉換為CASE WHEN如下所示,可完全了解此函數的特性。但此種寫法與一般常用除零判斷不同,鮮少使用。

CASE WHEN expr1 = expr 2 THEN NULL
ELSE expr1
END


若對於NULLIF函數難以理解,建議可將NULLIF函數拆解成NULL IF,即如果成立(等於0)即傳回NULL

1 則留言:

  1. 謝謝博主!最近剛好需要用SQL處理數據,偶遇這個博客,必須收藏之!

    回覆刪除