Oracle多版本执行日期分组查询遇ORA-01722:无效数字错误求助

你遇到的这个错误不是Oracle的Bug，核心原因是查询语句中别名与表列名重名，导致Oracle解析逻辑混淆。

具体来说：

• 你在SELECT子句中将to_char(log_time, 'YYYYMMDD HH24:MI')的结果别名为log_time，这个别名和原表的log_time（DATE类型列）完全同名。
• 当执行ORDER BY to_char(log_time, 'YYYYMMDD HH24:MI')时，Oracle会优先将这里的log_time识别为SELECT子句中定义的字符串别名，而非原表的DATE类型列。
• to_char函数接收字符串参数时，会尝试将其隐式转换为数字类型（因为to_char对数字和日期的处理逻辑不同），但'YYYYMMDD HH24:MI'格式的字符串（比如'20240520 14:30'）无法转换为有效数字，因此触发ORA-01722: invalid number错误。

解决办法 #

有两种简单的修正方式：

1. 修改别名，避免与表列名重名

   select      to_char(log_time, 'YYYYMMDD HH24:MI') log_time_str, 
               count (*)
   from        mytable 
   group by    to_char(log_time, 'YYYYMMDD HH24:MI')
   order  by   log_time_str
   

2. 在ORDER BY的to_char函数中明确指定表列的归属

   select      to_char(log_time, 'YYYYMMDD HH24:MI') log_time, 
               count (*)
   from        mytable 
   group by    to_char(log_time, 'YYYYMMDD HH24:MI')
   order  by   to_char(mytable.log_time, 'YYYYMMDD HH24:MI')
   

内容的提问来源于stack exchange，提问作者tahzibi.jafar