执行SQL查询出现numeric value is not recognized报错如何解决

报错原因 #

• 首先是比较逻辑的隐式转换问题：Col1是VARCHAR字符串类型，你在case when判断中直接写Col1 > 100，数据库会默认尝试把Col1的所有字符串值隐式转换为数值类型再和100比较，只要Col1里存在无法转换为数字的字符串值，就会直接抛出numeric value is not recognized报错。
• 其次是返回值的类型不统一问题：同一个case when的两个分支返回值类型不一致，满足条件时返回的是concat拼接得到的字符串，不满足条件时返回的是数值0，数据库会尝试隐式转换统一类型，也有概率触发数值识别报错，同时还会影响count distinct统计结果的准确性。

解决方法 #

你可以按两个核心原则修改SQL：一是显式处理Col1的类型转换，避免隐式转换失败；二是统一case when所有分支的返回值类型，避免跨类型转换异常。
修改后的参考SQL如下（如果你的数据库不支持TRY_CAST，可以替换为对应数据库的安全转数值函数）：

SELECT COUNT(DISTINCT(
  CASE 
    -- 先安全转换Col1为数值，转失败的会返回NULL，不会触发报错
    WHEN TRY_CAST(Col1 AS NUMBER) > 100 
    THEN CONCAT(Col2, CAST(Col3 AS STRING))
    -- 统一返回字符串类型，和前面分支的返回值类型对齐
    ELSE '0'
  END
))

如果你不需要统计else分支的内容，也可以直接把不符合条件的行返回NULL，count distinct会自动忽略NULL值，还能减少统计开销：

SELECT COUNT(DISTINCT(
  CASE 
    WHEN TRY_CAST(Col1 AS NUMBER) > 100 
    THEN CONCAT(Col2, CAST(Col3 AS STRING))
    ELSE NULL
  END
))

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