Oracle 11.2.0.4.0. error ORA-01791 running SELECT DISTINCT TO_CHAR(...) query
hi,
Im managing a WPF (.NET technology) application, connected to a Oracle 11.2.0.4.0 DB.
Last week, DB managers have upgraded old 11.2.0.1.0 DB to 11.2.0.4.0.
Now, users reported the above error on running a simple query like this
SELECT DISTINCT TO_CHAR(T.DATETIME,'DD/MM/RRRR HH24:MI') AS DATETIME, T.ID,....
FROM MY_TAB T
WHERE .....
ORDER BY T.DATETIME DESC, T.ID;
ORA-01791 - NOT A SELECTED EXPRESSION!
Note that if I replace ORDER BY T.DATETIME with ORDER BY DATETIME, query works, but data sorting is wrong (string sorting may not be matching datetime sorting).
Excepting DB version upgrade, there are no reasons for this issue to be raised (query was working on 11.2.0.1.0); and I wish to fix it avoiding having to re-compile and deploy the whole application.