ORA-00904: 无效标识符

2021-12-01 00:00:00 sql database oracle ora-00904

我尝试使用 Oracle 数据库编写以下内部联接查询:

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

出现以下错误:

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

一张表的DDL为:

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

推荐答案

你的问题是那些有害的双引号.

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 允许我们忽略数据库对象名称的大小写，前提是我们创建的名称全部为大写，或者不使用双引号.如果我们在脚本中使用混合大小写或小写，并将标识符用双引号括起来，那么每当我们引用对象或其属性时，我们都必须使用双引号和精确大小写:

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

不要在 DDL 脚本中使用双引号

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

... 或

select * from PS_TBL_DEPARTMENT_DETAILS;

... 或

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

相关文章