ORA-01830错误排查：十进制数值转英文单词SQL语句修正

首先，你碰到的ORA-01830错误，核心问题出在：TO_DATE的J格式模型（儒略日）只接受纯整数数值，但你的HIGH字段是带小数的十进制数（比如100.10），直接传入非整数会导致格式匹配失败，触发这个报错。

要解决这个问题，我们需要把金额拆成整数部分和小数部分分别转换，再拼接成符合英文金额表述习惯的内容（比如"ONE HUNDRED DOLLARS AND TEN CENTS"）。下面是修正后的SQL：

SELECT 
    SYMBOL,
    HIGH,
    -- 组合整数和小数部分的英文表述
    CASE 
        WHEN TRUNC(HIGH) = 0 THEN '' 
        ELSE UPPER(TO_CHAR(TO_DATE(TRUNC(HIGH), 'J'), 'JSP') || ' DOLLARS')
    END ||
    CASE 
        WHEN MOD(ROUND(HIGH * 100, 0), 100) = 0 THEN ''
        ELSE ' AND ' || UPPER(TO_CHAR(TO_DATE(MOD(ROUND(HIGH * 100, 0), 100), 'J'), 'JSP') || ' CENTS')
    END AS AMT_IN_WORDS
FROM BHAV;

关键逻辑说明： #

1. 拆分金额：
   • TRUNC(HIGH)：提取金额的整数部分（比如100.10的整数部分是100）
   • ROUND(HIGH * 100, 0)：先把小数部分转成整数（避免浮点精度误差），再用MOD(...,100)提取两位小数的数值（比如100.10的小数部分转成10）
2. 分别转换：用TO_DATE(整数,'J')+'JSP'格式把整数转成英文单词，再加上对应的货币单位（DOLLARS/CENTS，你可以根据实际需求替换成其他单位，比如RUPEES/PAISE）
3. 边界处理：通过CASE语句处理两种特殊情况：
   • 整数部分为0时，不显示DOLLARS相关内容
   • 小数部分为0时，不显示AND CENTS相关内容

额外注意事项： #

• 如果你的HIGH字段可能有超过两位的小数，一定要用ROUND(HIGH,2)先统一精度，避免小数部分位数混乱
• 儒略日的有效范围是1到5373484，这个范围几乎覆盖了所有常规金额的整数部分，所以不用担心超出限制的问题

内容的提问来源于stack exchange，提问作者N. kotadia