ORA-00904 invalid identifier

Question

I am getting this error while doing an  inner join query using the oracle database:

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

Inner join query:

 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

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

Can someone help me with this?

Answer 1

The issue is the 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>

It's recommended that the database object names be in upper case, or without using double quotes. see this code for reference :

 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>

I hope this helps.