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

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'