ORA-00904: 无效的标识符

2022-01-30 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'

相关文章