ORA-00904 for ORDER BY in Oracle

Question

I can not understand what goes wrong in this query:

    select last_name, first_name a from employees
order by "a";

Output is:

ORA-00904: "a": invalid identifier

However this query working and sort results by first_name in ascending order:

    select last_name, first_name a from employees
order by "A";

Answer 1

Oracle isn't case sensitive by default.

So, when you say

select first_name a from employees

Oracle sees that as

SELECT FIRST_NAME A FROM EMPLOYEES

but when you start using "Quotes"...

order by "a"

Oracle sees that as

ORDER BY "a"

a <> A

This isn't a problem if you ensure your quoted objects are also always capitalized, which is why your "A" works but your "a" doesn't.

My advice - just completely remove the quotes on your object names.

SELECT LAST_NAME,
       FIRST_NAME A
  FROM EMPLOYEES
 ORDER BY A;