Oracle执行含MIN()的CAST转换查询报错ORA-01722

原因 #

近期你的prev_table里新增了非数字格式的id记录（比如含字母、特殊符号、空白字符等）。Oracle的查询优化器在执行带MIN()的查询时，会扫描全表并尝试对所有行执行CAST(id AS INTEGER)转换，碰到非数字值就触发ORA-01722错误。而移除MIN()时，Oracle可能没有扫描全表（比如仅返回部分结果），刚好没碰到那些非法记录，所以看起来查询正常。

解决方案 #

方案1：先过滤合法数字再转换聚合 #

先筛选出能正常转为整数的id，再执行转换和取最小值操作，兼容性好（支持所有Oracle版本）：

SELECT MIN(CAST(id AS INTEGER)) AS min_id
FROM prev_table
WHERE id IS NOT NULL
  AND TRIM(id) <> ''
  -- 匹配纯数字字符串，如需支持负数可调整正则为'^-?[0-9]+$'
  AND REGEXP_LIKE(TRIM(id), '^[0-9]+$')

方案2：使用带错误处理的转换函数（Oracle 12cR2+） #

如果你的Oracle版本是12cR2及以上，可以用TO_NUMBER的错误处理语法，转换失败的记录会返回NULL，MIN()会自动忽略这些NULL值：

SELECT MIN(TO_NUMBER(id DEFAULT NULL ON CONVERSION ERROR)) AS min_id
FROM prev_table

内容的提问来源于stack exchange，提问作者S Roshan