Getting ORA-01722: invalid number error, When executing query

Question

In 3 versions of Orale (11g, 12c, 21c) I create the mytable as follows :

create table mytable (id number, log_time date)

and, then I inserted some data into the table,

insert into mytable values (1, sysdate);
insert into mytable values (2, sysdate);

after that I executed the following query:

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(log_time, 'YYYYMMDD HH24:MI')

But I get ORA-01722: invalid number error.

Is it bug or something else?

Answer 1

When you define the alias log_time in the SELECT clause and then use log_time again in the ORDER BY clause then you are referring to the aliased value and not to the underlying table value.

When you use order by to_char(log_time, 'YYYYMMDD HH24:MI') the query is effectively the same as:

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(to_char(mytable.log_time, 'YYYYMMDD HH24:MI'), 'YYYYMMDD HH24:MI')

Because you are applying TO_CHAR to the aliased value which has already had TO_CHAR applied to it in the SELECT clause. The TO_CHAR function takes either a NUMBER or a DATE as the first argument and the initial TO_CHAR in the SELECT generates a string so there is then an implicit cast for the second TO_CHAR in the ORDER BY clause and that defaults to trying to convert the string to a NUMBER - which fails with the error you are seeing.

---

To solve it, you want to either just use the alias:

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

or, explicitly use mytable.log_time to refer to the underlying table value (and not the alias):

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')

Which, for the sample data, both output:

LOG_TIME	COUNT(*)
20240515 13:21	2

fiddle