查询ORA-01722无效数字错误：id_case为空或小于1时出错

错误根源是子查询中TO_NUMBER(replace(p.description, 'xyy_', ''))和TO_NUMBER(replace(p.description, 'xyz_', ''))的转换逻辑——部分p.description替换后不是有效数字字符串，Oracle会优先执行子查询，转换失败直接触发错误，和外层过滤条件无关。

以下是具体解决方法：

1. 提前过滤无效数据 #

在子查询的WHERE条件中，用正则先判断替换后的字符串是否为纯数字，排除无法转换的行：

SELECT v.id, v.id_case_p, v.id_case 
FROM (
    SELECT p.id, 
           TO_NUMBER(replace(p.description,'xyy_','')) AS id_case_p, 
           c.id AS id_case 
    FROM t_processes p 
    LEFT JOIN t_cases c 
        ON TO_NUMBER(replace(p.description,'xyz_','')) = c.id 
    WHERE p.active = 'Y' 
      AND p.id_process_step < 10 
      -- 只保留替换后是纯数字的记录
      AND REGEXP_LIKE(replace(p.description,'xyy_',''), '^[0-9]+$')
      AND REGEXP_LIKE(replace(p.description,'xyz_',''), '^[0-9]+$')
)v 
WHERE v.id_case < 1 OR v.id_case IS NULL;

2. 使用安全转换函数（Oracle 12c+） #

用VALIDATE_CONVERSION函数先判断字符串能否转成数字，转换失败返回NULL而非报错，避免中断查询：

SELECT v.id, v.id_case_p, v.id_case 
FROM (
    SELECT p.id, 
           CASE 
               WHEN VALIDATE_CONVERSION(replace(p.description,'xyy_','') AS NUMBER) = 1 
               THEN TO_NUMBER(replace(p.description,'xyy_','')) 
               ELSE NULL 
           END AS id_case_p, 
           c.id AS id_case 
    FROM t_processes p 
    LEFT JOIN t_cases c 
        ON CASE 
               WHEN VALIDATE_CONVERSION(replace(p.description,'xyz_','') AS NUMBER) = 1 
               THEN TO_NUMBER(replace(p.description,'xyz_','')) 
               ELSE NULL 
           END = c.id 
    WHERE p.active = 'Y' 
      AND p.id_process_step < 10 
)v 
WHERE v.id_case < 1 OR v.id_case IS NULL;

3. 调整关联逻辑（避免数字转换） #

将关联条件中的数字转换改为字符串匹配，把t_cases.id转成字符串和替换后的description对比：

SELECT v.id, v.id_case_p, v.id_case 
FROM (
    SELECT p.id, 
           CASE 
               WHEN REGEXP_LIKE(replace(p.description,'xyy_',''), '^[0-9]+$')
               THEN TO_NUMBER(replace(p.description,'xyy_','')) 
               ELSE NULL 
           END AS id_case_p, 
           c.id AS id_case 
    FROM t_processes p 
    LEFT JOIN t_cases c 
        ON replace(p.description,'xyz_','') = TO_CHAR(c.id)
    WHERE p.active = 'Y' 
      AND p.id_process_step < 10 
)v 
WHERE v.id_case < 1 OR v.id_case IS NULL;

注意：此方法需确保TO_CHAR(c.id)的格式和replace(p.description,'xyz_','')完全一致（比如无额外前导零）。

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