ORA-00904: 无效的标识符
ORA-00904: 无效的标识符
我尝试使用Oracle数据库编写以下内连接查询:
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
admin 更改状态以发布 2023年5月24日
你的问题是那些有害的双引号。
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允许我们忽略数据库对象名称的大小写,只要我们创建它们的名称全部使用大写或不使用双引号。如果我们在脚本中使用混合大小写或小写,并用双引号包装标识符,那么我们就必须使用双引号和精确的大小写来引用对象或其属性:
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脚本中不要使用双引号。
(我知道大多数第三方代码生成器都这样做,但他们足够有纪律把所有对象名称都写成大写字母。)
反之亦然。如果我们创建表时不使用双引号...
create table PS_TBL_DEPARTMENT_DETAILS ( company_code VARCHAR2(255), company_name VARCHAR2(255), Cost_Center_Number VARCHAR2(255)) ;
...我们可以按照自己的喜好引用它及其列:
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'