Oracle Sql Developer中GROUP BY查询报invalid number错误如何解决

报错原因 #

• 核心原因是Oracle执行隐式数据类型转换时遇到了无法转换为数字的字符串值，触发ORA-01722 invalid number错误，你核查字段类型时大概率忽略了关联/比较字段的类型差异：
  1. 关联条件的字段类型不匹配：比如JOIN COD_CUSTOMER_MASTER E ON E.CUS_ID=C.ALLOTTE中，如果E.CUS_ID是NUMBER类型、C.ALLOTTE是VARCHAR2类型，Oracle会自动把C.ALLOTTE的所有值转为数字匹配，一旦C.ALLOTTE存在非数字内容（比如字母、空格、特殊符号）就会报错；同理C.FORM_NO NOT IN (SELECT REG_NO FROM COD_RECOVERY_RECORD)中如果FORM_NO和REG_NO类型不一致也会触发相同问题。
  2. 比较运算的不必要隐式转换：你代码中COUNT(...) > '2'、E.PROJECT_ID='4'、E.COMPANY_ID='2'等语句，把数字值用单引号包裹为字符串类型，Oracle会自动把字符串转为数字比较，叠加其他隐式转换逻辑时会提升报错概率。

解决方案 #

• 第一步：去掉所有数字值的多余单引号，避免不必要的隐式转换：

  -- 修改前
  (SELECT COUNT(B.SALE_ID) ... ) > '2' 
  AND E.PROJECT_ID='4'
  AND E.COMPANY_ID='2'
  -- 修改后
  (SELECT COUNT(B.SALE_ID) ... ) > 2 
  AND E.PROJECT_ID=4
  AND E.COMPANY_ID=2
  

• 第二步：统一关联条件的字段类型，用显式转换替代隐式转换：
  1. 检查C.ALLOTTE和E.CUS_ID的类型，如C.ALLOTTE为字符串、E.CUS_ID为数字，修改关联逻辑：

  -- 转换ALLOTTE为数字，转换失败时自动跳过匹配，适合Oracle 12c及以上版本
  JOIN COD_CUSTOMER_MASTER E ON E.CUS_ID = TO_NUMBER(C.ALLOTTE DEFAULT -1 ON CONVERSION ERROR)
  -- 低版本Oracle可以先过滤非数字的ALLOTTE记录
  JOIN COD_CUSTOMER_MASTER E ON E.CUS_ID = TO_NUMBER(C.ALLOTTE)
  WHERE REGEXP_LIKE(C.ALLOTTE, '^[0-9]+$')
  

  2. 检查C.FORM_NO和REG_NO的类型，如类型不一致统一显式转换：

  -- 例：FORM_NO为字符串、REG_NO为数字时，把REG_NO转成字符串匹配
  AND C.FORM_NO NOT IN (SELECT TO_CHAR(REG_NO) FROM COD_RECOVERY_RECORD)
  

• 第三步：可选优化，日期比较加显式转换避免会话参数异常：

  WHERE A.DUE_DATE < TO_DATE('20-AUG-21', 'DD-MON-RR', 'NLS_DATE_LANGUAGE=AMERICAN')
  

• 排查脏数据：如果仍报错可以用以下语句查询存在非数字内容的字段：

  -- 排查ALLOTTE字段的非数字值
  SELECT ALLOTTE FROM COD_BOOKING_MASTER WHERE REGEXP_LIKE(ALLOTTE, '[^0-9]');
  -- 排查FORM_NO字段的非数字值
  SELECT FORM_NO FROM COD_BOOKING_MASTER WHERE REGEXP_LIKE(FORM_NO, '[^0-9]');
  

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