ORA-00904: invalid identifier

Solution 1:

Your problem is those pernicious double quotes.

SQL> CREATE TABLE "APC"."PS_TBL_DEPARTMENT_DETAILS"
  2  (
  3    "Company Code" VARCHAR2(255),
  4    "Company Name" VARCHAR2(255),
  5    "Sector_Code" VARCHAR2(255),
  6    "Sector_Name" VARCHAR2(255),
  7    "Business_Unit_Code" VARCHAR2(255),
  8    "Business_Unit_Name" VARCHAR2(255),
  9    "Department_Code" VARCHAR2(255),
 10    "Department_Name" VARCHAR2(255),
 11    "HR_ORG_ID" VARCHAR2(255),
 12    "HR_ORG_Name" VARCHAR2(255),
 13    "Cost_Center_Number" VARCHAR2(255),
 14    " " VARCHAR2(255)
 15  )
 16  /

Table created.

SQL>

Oracle SQL allows us to ignore the case of database object names provided we either create them with names all in upper case, or without using double quotes. If we use mixed case or lower case in the script and wrapped the identifiers in double quotes we are condemned to using double quotes and the precise case whenever we refer to the object or its attributes:

SQL> select count(*) from PS_TBL_DEPARTMENT_DETAILS
  2  where Department_Code = 'BAH'
  3  /
where Department_Code = 'BAH'
      *
ERROR at line 2:
ORA-00904: "DEPARTMENT_CODE": invalid identifier


SQL> select count(*) from PS_TBL_DEPARTMENT_DETAILS
  2  where "Department_Code" = 'BAH'
  3  /

  COUNT(*)
----------
         0

SQL>

tl;dr

don't use double quotes in DDL scripts

(I know most third party code generators do, but they are disciplined enough to put all their object names in UPPER CASE.)

The reverse is also true. If we create the table without using double-quotes …

create table PS_TBL_DEPARTMENT_DETAILS
( company_code VARCHAR2(255),
  company_name VARCHAR2(255),
  Cost_Center_Number VARCHAR2(255))
;

… we can reference it and its columns in whatever case takes our fancy:

select * from ps_tbl_department_details

… or

select * from PS_TBL_DEPARTMENT_DETAILS;

… or

select * from PS_Tbl_Department_Details
where COMAPNY_CODE = 'ORCL'
and cost_center_number = '0980'

Solution 2:

In my case, this error occurred, due to lack of existence of column name in the table.

When i executed "describe tablename" , i was not able to find the column specified in the mapping hbm file.

After altering the table, it worked fine.

Solution 3:

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.