Oracle中如何根据条件调整数值的小数点位置？

问题分析 #

需要根据Country字段调整Value列的小数点位置：USA的数值需左移一位（即除以10）并保留三位小数，其他国家保留两位小数。原SQL存在参数误用问题，且使用TO_CHAR时触发ORA-01722: invalid number错误。

错误原因 #

• 原SQL中TO_NUMBER(value,10,3)是错误用法：TO_NUMBER函数的参数为字符串、格式掩码、NLS参数，并非精度和小数位。Value本身是NUMBER类型，无需用TO_NUMBER转换，直接对数值计算即可。
• ORA-01722错误通常因字符串转数值时格式不匹配导致，没必要通过TO_CHAR绕弯处理数值运算，直接操作数值可避免此类错误。

正确解决方案 #

方案1：使用ROUND函数（推荐） #

通过数学运算调整小数点位置，再用ROUND控制小数位数：

SELECT
    PersonID,
    Country,
    CASE
        WHEN Country = 'USA' THEN ROUND(Value / 10, 3)
        ELSE ROUND(Value, 2)
    END AS Value
FROM emp_country;

• 对USA的数值执行Value / 10实现小数点左移一位，ROUND(...,3)确保保留三位小数；
• 其他国家直接用ROUND(...,2)保留两位小数，完全匹配期望输出。

方案2：使用CAST指定数值类型 #

若需严格定义输出的数值精度，可使用CAST转换类型：

SELECT
    PersonID,
    Country,
    CASE
        WHEN Country = 'USA' THEN CAST(Value / 10 AS NUMBER(10, 3))
        ELSE CAST(Value AS NUMBER(10, 2))
    END AS Value
FROM emp_country;

CAST会自动将数值四舍五入到指定的小数位，效果与ROUND一致，但明确了输出的数值精度范围。

验证结果 #

执行上述SQL后，输出将符合期望：

内容的提问来源于stack exchange，提问作者Shri Nivi