ORA-00904: invalid identifier

Question

I tried to write the following inner join query using an Oracle database:

 SELECT Employee.EMPLID as EmpID, 
        Employee.FIRST_NAME AS Name,
        Team.DEPARTMENT_CODE AS TeamID, 
        Team.Department_Name AS teamname
 FROM PS_TBL_EMPLOYEE_DETAILS Employee
 INNER JOIN PS_TBL_DEPARTMENT_DETAILS Team 
 ON Team.DEPARTMENT_CODE = Employee.DEPTID

That gives the below error:

 INNER JOIN PS_TBL_DEPARTMENT_DETAILS Team ON Team.DEPARTMENT_CODE = Employee.DEPTID
                                              *
ERROR at line 4:
ORA-00904: "TEAM"."DEPARTMENT_CODE": invalid identifier

The DDL of one table is:

CREATE TABLE "HRMS"."PS_TBL_DEPARTMENT_DETAILS"
(
  "Company Code" VARCHAR2(255),
  "Company Name" VARCHAR2(255),
  "Sector_Code" VARCHAR2(255),
  "Sector_Name" VARCHAR2(255),
  "Business_Unit_Code" VARCHAR2(255),
  "Business_Unit_Name" VARCHAR2(255),
  "Department_Code" VARCHAR2(255),
  "Department_Name" VARCHAR2(255),
  "HR_ORG_ID" VARCHAR2(255),
  "HR_ORG_Name" VARCHAR2(255),
  "Cost_Center_Number" VARCHAR2(255),
  " " VARCHAR2(255)
)
SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS

Answer 1

Also make sure the user issuing the query has been granted the necessary permissions.

For queries on tables you need to grant SELECT permission.
For queries on other object types (e.g. stored procedures) you need to grant EXECUTE permission.

Answer 2

Are you sure you have a column DEPARTEMENT_CODE on your table PS_TBL_DEPARTMENT_DETAILS

More informations about your ERROR

ORA-00904: string: invalid identifier Cause: The column name entered is either missing or invalid. Action: Enter a valid column name. A valid column name must begin with a letter, be less than or equal to 30 characters, and consist of only alphanumeric characters and the special characters $, _, and #. If it contains other characters, then it must be enclosed in d double quotation marks. It may not be a reserved word.

Answer 3

I had the same exception in JPA 2 using eclipse link. I had an @embedded class with one to one relationship with an entity. By mistake ,in the embedded class, i had also the annotation @Table("TRADER"). When the DB was created by the JPA from the entities it also created a table TRADER (which was a wrong as the Trader entity was embedded to the main entity) and the existence of that table was causing the above exception every time i was trying to persist my entity. After deleting the TRADER table the exception disappered.

Answer 4

I was passing the values without the quotes. Once I passed the conditions inside the single quotes worked like a charm.

Select * from emp_table where emp_id=123;

instead of the above use this:

Select * from emp_table where emp_id='123';

Answer 5

DEPARTMENT_CODE is not a column that exists in the table Team. Check the DDL of the table to find the proper column name.

Answer 6

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.