FYI, in this case the cause was found to be mixed case column name in the DDL for table creation.
However, if you are mixing «old style» and ANSI joins you could get the same error message even when the DDL was done properly with uppercase table name. This happened to me, and google sent me to this stackoverflow page so I thought I’d share since I was here.
--NO PROBLEM: ANSI syntax
SELECT A.EMPLID, B.FIRST_NAME, C.LAST_NAME
FROM PS_PERSON A
INNER JOIN PS_NAME_PWD_VW B ON B.EMPLID = A.EMPLID
INNER JOIN PS_HCR_PERSON_NM_I C ON C.EMPLID = A.EMPLID
WHERE
LENGTH(A.EMPLID) = 9
AND LENGTH(B.LAST_NAME) > 5
AND LENGTH(C.LAST_NAME) > 5
ORDER BY 1, 2, 3
/
--NO PROBLEM: OLD STYLE/deprecated/traditional oracle proprietary join syntax
SELECT A.EMPLID, B.FIRST_NAME, C.LAST_NAME
FROM PS_PERSON A
, PS_NAME_PWD_VW B
, PS_HCR_PERSON_NM_I C
WHERE
B.EMPLID = A.EMPLID
and C.EMPLID = A.EMPLID
and LENGTH(A.EMPLID) = 9
AND LENGTH(B.LAST_NAME) > 5
AND LENGTH(C.LAST_NAME) > 5
ORDER BY 1, 2, 3
/
The two SQL statements above are equivalent and produce no error.
When you try to mix them you can get lucky, or you can get an Oracle has a ORA-00904 error.
--LUCKY: mixed syntax (ANSI joins appear before OLD STYLE)
SELECT A.EMPLID, B.FIRST_NAME, C.LAST_NAME
FROM
PS_PERSON A
inner join PS_HCR_PERSON_NM_I C on C.EMPLID = A.EMPLID
, PS_NAME_PWD_VW B
WHERE
B.EMPLID = A.EMPLID
and LENGTH(A.EMPLID) = 9
AND LENGTH(B.FIRST_NAME) > 5
AND LENGTH(C.LAST_NAME) > 5
/
--PROBLEM: mixed syntax (OLD STYLE joins appear before ANSI)
--http://sqlfascination.com/2013/08/17/oracle-ansi-vs-old-style-joins/
SELECT A.EMPLID, B.FIRST_NAME, C.LAST_NAME
FROM
PS_PERSON A
, PS_NAME_PWD_VW B
inner join PS_HCR_PERSON_NM_I C on C.EMPLID = A.EMPLID
WHERE
B.EMPLID = A.EMPLID
and LENGTH(A.EMPLID) = 9
AND LENGTH(B.FIRST_NAME) > 5
AND LENGTH(C.LAST_NAME) > 5
/
And the unhelpful error message that doesn’t really describe the problem at all:
>[Error] Script lines: 1-12 -------------------------
ORA-00904: "A"."EMPLID": invalid identifier Script line 6, statement line 6,
column 51
I was able to find some research on this in the following blog post:
- http://sqlfascination.com/2013/08/17/oracle-ansi-vs-old-style-joins/
In my case, I was attempting to manually convert from old style to ANSI style joins, and was doing so incrementally, one table at a time. This appears to have been a bad idea. Instead, it’s probably better to convert all tables at once, or comment out a table and its where conditions in the original query in order to compare with the new ANSI query you are writing.
FYI, in this case the cause was found to be mixed case column name in the DDL for table creation.
However, if you are mixing «old style» and ANSI joins you could get the same error message even when the DDL was done properly with uppercase table name. This happened to me, and google sent me to this stackoverflow page so I thought I’d share since I was here.
--NO PROBLEM: ANSI syntax
SELECT A.EMPLID, B.FIRST_NAME, C.LAST_NAME
FROM PS_PERSON A
INNER JOIN PS_NAME_PWD_VW B ON B.EMPLID = A.EMPLID
INNER JOIN PS_HCR_PERSON_NM_I C ON C.EMPLID = A.EMPLID
WHERE
LENGTH(A.EMPLID) = 9
AND LENGTH(B.LAST_NAME) > 5
AND LENGTH(C.LAST_NAME) > 5
ORDER BY 1, 2, 3
/
--NO PROBLEM: OLD STYLE/deprecated/traditional oracle proprietary join syntax
SELECT A.EMPLID, B.FIRST_NAME, C.LAST_NAME
FROM PS_PERSON A
, PS_NAME_PWD_VW B
, PS_HCR_PERSON_NM_I C
WHERE
B.EMPLID = A.EMPLID
and C.EMPLID = A.EMPLID
and LENGTH(A.EMPLID) = 9
AND LENGTH(B.LAST_NAME) > 5
AND LENGTH(C.LAST_NAME) > 5
ORDER BY 1, 2, 3
/
The two SQL statements above are equivalent and produce no error.
When you try to mix them you can get lucky, or you can get an Oracle has a ORA-00904 error.
--LUCKY: mixed syntax (ANSI joins appear before OLD STYLE)
SELECT A.EMPLID, B.FIRST_NAME, C.LAST_NAME
FROM
PS_PERSON A
inner join PS_HCR_PERSON_NM_I C on C.EMPLID = A.EMPLID
, PS_NAME_PWD_VW B
WHERE
B.EMPLID = A.EMPLID
and LENGTH(A.EMPLID) = 9
AND LENGTH(B.FIRST_NAME) > 5
AND LENGTH(C.LAST_NAME) > 5
/
--PROBLEM: mixed syntax (OLD STYLE joins appear before ANSI)
--http://sqlfascination.com/2013/08/17/oracle-ansi-vs-old-style-joins/
SELECT A.EMPLID, B.FIRST_NAME, C.LAST_NAME
FROM
PS_PERSON A
, PS_NAME_PWD_VW B
inner join PS_HCR_PERSON_NM_I C on C.EMPLID = A.EMPLID
WHERE
B.EMPLID = A.EMPLID
and LENGTH(A.EMPLID) = 9
AND LENGTH(B.FIRST_NAME) > 5
AND LENGTH(C.LAST_NAME) > 5
/
And the unhelpful error message that doesn’t really describe the problem at all:
>[Error] Script lines: 1-12 -------------------------
ORA-00904: "A"."EMPLID": invalid identifier Script line 6, statement line 6,
column 51
I was able to find some research on this in the following blog post:
- http://sqlfascination.com/2013/08/17/oracle-ansi-vs-old-style-joins/
In my case, I was attempting to manually convert from old style to ANSI style joins, and was doing so incrementally, one table at a time. This appears to have been a bad idea. Instead, it’s probably better to convert all tables at once, or comment out a table and its where conditions in the original query in order to compare with the new ANSI query you are writing.
FYI, in this case the cause was found to be mixed case column name in the DDL for table creation.
However, if you are mixing «old style» and ANSI joins you could get the same error message even when the DDL was done properly with uppercase table name. This happened to me, and google sent me to this stackoverflow page so I thought I’d share since I was here.
--NO PROBLEM: ANSI syntax
SELECT A.EMPLID, B.FIRST_NAME, C.LAST_NAME
FROM PS_PERSON A
INNER JOIN PS_NAME_PWD_VW B ON B.EMPLID = A.EMPLID
INNER JOIN PS_HCR_PERSON_NM_I C ON C.EMPLID = A.EMPLID
WHERE
LENGTH(A.EMPLID) = 9
AND LENGTH(B.LAST_NAME) > 5
AND LENGTH(C.LAST_NAME) > 5
ORDER BY 1, 2, 3
/
--NO PROBLEM: OLD STYLE/deprecated/traditional oracle proprietary join syntax
SELECT A.EMPLID, B.FIRST_NAME, C.LAST_NAME
FROM PS_PERSON A
, PS_NAME_PWD_VW B
, PS_HCR_PERSON_NM_I C
WHERE
B.EMPLID = A.EMPLID
and C.EMPLID = A.EMPLID
and LENGTH(A.EMPLID) = 9
AND LENGTH(B.LAST_NAME) > 5
AND LENGTH(C.LAST_NAME) > 5
ORDER BY 1, 2, 3
/
The two SQL statements above are equivalent and produce no error.
When you try to mix them you can get lucky, or you can get an Oracle has a ORA-00904 error.
--LUCKY: mixed syntax (ANSI joins appear before OLD STYLE)
SELECT A.EMPLID, B.FIRST_NAME, C.LAST_NAME
FROM
PS_PERSON A
inner join PS_HCR_PERSON_NM_I C on C.EMPLID = A.EMPLID
, PS_NAME_PWD_VW B
WHERE
B.EMPLID = A.EMPLID
and LENGTH(A.EMPLID) = 9
AND LENGTH(B.FIRST_NAME) > 5
AND LENGTH(C.LAST_NAME) > 5
/
--PROBLEM: mixed syntax (OLD STYLE joins appear before ANSI)
--http://sqlfascination.com/2013/08/17/oracle-ansi-vs-old-style-joins/
SELECT A.EMPLID, B.FIRST_NAME, C.LAST_NAME
FROM
PS_PERSON A
, PS_NAME_PWD_VW B
inner join PS_HCR_PERSON_NM_I C on C.EMPLID = A.EMPLID
WHERE
B.EMPLID = A.EMPLID
and LENGTH(A.EMPLID) = 9
AND LENGTH(B.FIRST_NAME) > 5
AND LENGTH(C.LAST_NAME) > 5
/
And the unhelpful error message that doesn’t really describe the problem at all:
>[Error] Script lines: 1-12 -------------------------
ORA-00904: "A"."EMPLID": invalid identifier Script line 6, statement line 6,
column 51
I was able to find some research on this in the following blog post:
- http://sqlfascination.com/2013/08/17/oracle-ansi-vs-old-style-joins/
In my case, I was attempting to manually convert from old style to ANSI style joins, and was doing so incrementally, one table at a time. This appears to have been a bad idea. Instead, it’s probably better to convert all tables at once, or comment out a table and its where conditions in the original query in order to compare with the new ANSI query you are writing.
Learn the cause and how to resolve the ORA-00904 error message in Oracle.
Description
When you encounter an ORA-00904 error, the following error message will appear:
- ORA-00904: invalid identifier
Cause
You tried to execute a SQL statement that included an invalid column name or the column name is missing. This commonly occurs when you reference an invalid alias in a SELECT statement.
Resolution
The option(s) to resolve this Oracle error are:
Option #1
Rewrite your SQL to include a valid column name. To be a valid column name the following criteria must be met:
- The column name must begin with a letter.
- The column name can not be longer than 30 characters.
- The column name must be made up of alphanumeric characters or the following special characters: $, _, and #. If the column name uses any other characters, it must be enclosed in double quotation marks.
- The column name can not be a reserved word.
Let’s look at an example of how to resolve an ORA-00904 error. For example, if you ran the following SELECT statement, you would receive 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 was created by 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 called it as «cid» in the ORDER BY clause.
To resolve this error, we can modify our SELECT statement to use the correct alias name 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