小数点以下があるフィールド同士を掛けて、その結果の小数点以下をROUNDで切り捨てた値を小数点のないフィールドに追加しようとするとエラーになる

Question

こんにちは、sugarwingと申します。

今回質問したいのは、小数点以下があるフィールド同士を掛けて、その結果の小数点以下をROUNDで切り捨てた値を小数点のないフィールドに追加しようとするとエラーになる現象が発生し、回避方法が見つからず困っています。

バージョン：SQL Server2008 Std
参照元フィールド：数量（DECIMAL(14,2)）と単価（DECIMAL(14,2)）
追加先フィールド：金額（DECIMAL(14,0)）
計算式：ROUND(数量*単価,0,1)
計算結果：（数量を12.2、単価を5.23とした場合）
　　　　　　　ROUND(12.2*5.23,0,1)=63.0000

エラーメッセージ：「メッセージ 8115、レベル 16、状態 7、行 15
                      numeric をデータ型 numeric に変換中に、算術オーバーフロー エラーが発生しました。」

何か良い解決方法があればご教授をお願い致します。

Answer 1

今回質問したいのは、小数点以下があるフィールド同士を掛けて、その結果の小数点以下をROUNDで切り捨てた値を小数点のないフィールドに追加しようとするとエラーになる現象が発生し、回避方法が見つからず困っています。

バージョン：SQL Server2008 Std
参照元フィールド：数量（DECIMAL(14,2)）と単価（DECIMAL(14,2)）
追加先フィールド：金額（DECIMAL(14,0)）
計算式：ROUND(数量*単価,0,1)
計算結果：（数量を12.2、単価を5.23とした場合）
　　　　　　　ROUND(12.2*5.23,0,1)=63.0000

エラーメッセージ：「メッセージ 8115、レベル 16、状態 7、行 15
                      numeric をデータ型 numeric に変換中に、算術オーバーフロー エラーが発生しました。」

 

調べてみました。データベースのプロパティのオプション、「数値処理丸めアボート」が True  になっていると発生するようですね。
以下もご覧下さい。

SET NUMERIC_ROUNDABORT

---

ひらぽん http://blogs.yahoo.co.jp/hilapon/

Answer 2

こんにちは、nagino です。

念のため、若干補足を。

DECIMAL(14, 2) と DECIMAL(14, 2) の演算結果は小数点以下 4 桁のデータになります。
これを ROUND すると小数点以下は 0 になりますが、データ型としては小数点以下 4 桁のデータになります。
（ROUND は丸める関数であって、キャストをする関数ではない）
ですので、一旦 BIGINT あたりの整数型に明示的にキャストしてしまうという手はあります。

イメージとしては

SELECT 金額 = CONVERT(DECIMAL(14, 0), CONVERT(BIGINT, ROUND(数量 * 単価)))
SELECT 金額 = CONVERT(BIGINT, ROUND(数量 * 単価))

といった感じでしょうか。
手元の環境では下のクエリでもエラーにならなかったのがちょっと疑問ですが・・・。

 

---

MCITP（Database Developer/Database Administrator）

Answer 3

ひらぽんさん

回答ありがとうございます。

上記設定にて回避できることは確認ができました。
ありがとうございます。

ただ、上記設定を変更した場合、精度が落ちると丸め処理が発生するとあるため、
金額を算出するロジックとしては使用しないほうが懸命であると考えます。

Answer 4

> ただ、上記設定を変更した場合、精度が落ちると丸め処理が発生するとあるため、
> 金額を算出するロジックとしては使用しないほうが懸命であると考えます。

だったら nagino さんが以下のように提示されてますように

> SELECT 金額 = CONVERT(DECIMAL(14, 0), CONVERT(BIGINT, ROUND(数量 * 単価)))

明示的に DECIMAL(14,0) にキャストすればいいのではないでしょうか。以下参考にどうぞ。

データ型の変換

---

ひらぽん http://blogs.yahoo.co.jp/hilapon/

Answer 5

SELECT 金額 = CONVERT(DECIMAL(14, 0), CONVERT(BIGINT, ROUND(数量 * 単価)))
SELECT 金額 = CONVERT(BIGINT, ROUND(数量 * 単価))

といった感じでしょうか。
手元の環境では下のクエリでもエラーにならなかったのがちょっと疑問ですが・・・。

 

---

MCITP（Database Developer/Database Administrator）

 

naginoさん

回答ありがとうございます。

やはりCASTもしくはCONVERTするのが正解なのでしょうか。

こちらの環境でもROUNDしたものをCASTでBIGINTにして金額フィールドにセットできることは確認しています。
BIGINTにすることで小数点以下がなくなるためにセットできるのではないかと思います。

小数点以下のある値を算出する際の有効桁数についても聞きたいことがありますので、別にスレッドを立てて質問いたします。

ありがとうございました。