Why Does My Oracle Query Result in ORA-00904: Invalid Identifier?

ORA-00904: Invalid Identifier error parsing

Problem description:

When executing an inner join query in Oracle database, the following error occurs:

<code class="language-sql">ORA-00904: "TEAM"."DEPARTMENT_CODE": invalid identifier</code>

Root Cause:

The root cause of the error is the use of double quotes in the table name and column name in the query. Oracle Database requires object names to be in all uppercase letters or without quotes.

Solution:

To resolve this issue, remove the double quotes from the table and column names in the query and also do not use double quotes when creating the table in the DDL script.

The correct SQL query is as follows:

<code class="language-sql">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</code>

Avoid using double quotes in DDL scripts:

Best practice is to avoid using double quotes in DDL scripts. It is recommended to use an all-caps name:

<code class="language-sql">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)
)</code>

This approach provides greater flexibility and avoids potential errors due to inconsistent casing. Note that the redundant " " columns in the example DDL have been removed.

The above is the detailed content of Why Does My Oracle Query Result in ORA-00904: Invalid Identifier?. For more information, please follow other related articles on the PHP Chinese website!