SQL Teradata - 计算期间发生数字溢出
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30220889/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Teradata - Numeric overflow occurred during computation
提问by craigtb
I am having an issue with a calculation in one of my Teradata queries. I am multiplying two numbers by each other but i am getting a "Numeric overflow occurred during computation." error when running the query. I ran a type on both fields and they are DECIMAL(18,15) and DECIMAL(18,9). I tried casting them both to DECIMAL(18,18) when i do the division but its still throwing errors. Here is the calculation. UNITS is the 18,15 and PRICE is the 18,9. Can anyone please give me any tips on how to resolve this?
我在我的 Teradata 查询之一中遇到计算问题。我将两个数字相乘，但出现“计算过程中发生数字溢出”。运行查询时出错。我在两个字段上都运行了一个类型，它们是 DECIMAL(18,15) 和 DECIMAL(18,9)。当我做除法时，我尝试将它们都转换为 DECIMAL(18,18) 但它仍然抛出错误。这是计算。UNITS 是 18,15，PRICE 是 18,9。任何人都可以给我任何有关如何解决此问题的提示吗？
cast(UNITS as DECIMAL(18,18))* cast(PRICE as DECIMAL(18,18)) as NEW_CALC
Thanks,
谢谢，
Craig
克雷格
回答by dnoeth
You use wrong datatypes, a DECIMAL(18,15)means 18 digits, 15 of them fractional, so the maximum value is 999.999999999999999.
您使用了错误的数据类型，aDECIMAL(18,15)表示 18 位数字，其中 15 位是小数，因此最大值为999.999999999999999。
And when you multiply two decimals, the number of fractional digits adds up, NEW_CALCresults in 38 fractional digits. Do a TYPE(cast(UNITS as DECIMAL(18,18))* cast(PRICE as DECIMAL(18,18))).
当您将两位小数相乘时，小数位数相加，NEW_CALC结果为 38 位小数。做一个TYPE(cast(UNITS as DECIMAL(18,18))* cast(PRICE as DECIMAL(18,18)))。
This will work:
这将起作用：
cast(UNITS as DECIMAL(38,15))* PRICE
But you better change the column's datatype to something like (18,2) or (18,4), I don't think anyone uses more than 4 digits for prices.
但是您最好将列的数据类型更改为 (18,2) 或 (18,4) 之类的内容，我认为没有人使用超过 4 位的价格。
