Learn the motive and how to resolve the ORA-00904 error message in Oracle.
When you stumble upon an ORA-00904 error, the following error message will appear:
ORA-00904: invalid identifier
You tried to execute a SQL announcement that covered an invalid column title or the column identify is missing. This typically takes place when you reference an invalid alias in a SELECT statement.
The option(s) to resolve this Oracle error are:
Rewrite your SQL to encompass a legitimate column name. To be a legitimate column title the following criteria have to be met:
The column name ought to start with a letter. The column name can no longer be longer than 30 characters. The column identify ought to be made up of alphanumeric characters or the following different characters: $, _, and #. If the column identify uses any other characters, it need to be enclosed in double quotation marks. The column name can now not be a reserved word.
Let’s look at an instance of how to resolve an ORA-00904 error. For example, if you ran the following SELECT statement, you would obtain an ORA-00904 error:
SQL> SELECT contact_id AS "c_id", last_name, first_name 2 FROM contacts 3 ORDER BY "cid"; ORDER BY "cid" * ERROR at line 3: ORA-00904: "cid": invalid identifier
This error used to be created with the aid of aliasing a column, but then mistyping the alias later. In this example, we created the alias called “c_id” for the contact_id, but then referred to as it as “cid” in the ORDER BY clause.
To resolve this error, we can alter our SELECT declaration to use the right alias identify in the ORDER BY clause as follows:
SQL> SELECT contact_id AS "c_id", last_name, first_name 2 FROM contacts 3 ORDER BY "c_id"; 10 rows selected