ORA-00904: invalid identifier

ORA-00904: "COLUMN_NAME": invalid identifier

Oracle cannot find a column or name used in the statement. Usually the column is misspelled, belongs to another table, was created in quotes with a different case, or is a SELECT-list alias used in the WHERE clause.

What it means

Every identifier in a SQL statement must resolve to a column, alias or function Oracle can see. When one does not, Oracle reports it in quotes — for example "EMP_NAME": invalid identifier — so the message itself tells you which name to check.

Common causes

A misspelled column name, or a column that exists in another table of the query.
A column created with double quotes in mixed case ("FirstName") and queried without quotes.
Using a SELECT-list alias in WHERE or GROUP BY, where it is not visible yet.
A reserved word used as a column name, or a column that was dropped or renamed.

How to fix it

Check the exact column names: DESC table_name or USER_TAB_COLUMNS.
If a column was created in quotes, query it in quotes with the same case — or rename it to upper case.
Repeat the expression instead of the alias in WHERE, or wrap the query in a subquery and filter outside.
Qualify columns with the table alias in joins so each name resolves to the right table.

Example

An alias is not visible in WHERE:

-- ORA-00904: "ANNUAL": invalid identifier
SELECT salary * 12 AS annual FROM employees WHERE annual > 100000;

-- Works
SELECT * FROM (SELECT salary * 12 AS annual FROM employees)
 WHERE annual > 100000;

In SQLlightspeed

When a statement fails, SQLlightspeed’s error panel shows the error code with a plain-language explanation and a suggestion for what to do next, together with Oracle’s own message and a link to Oracle’s documentation — for this error and dozens of other common ones.

Try SQLlightspeed free for 30 days

Oracle documentation for ORA-00904. Oracle is a registered trademark of Oracle Corporation. SQLlightspeed is not affiliated with Oracle.