SQL ORA-00904: 无效标识符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6027961/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ORA-00904: invalid identifier
提问by Navaneethan
I tried to write the following inner join query using an Oracle database:
我尝试使用 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
That gives the below error:
这给出了以下错误：
INNER JOIN PS_TBL_DEPARTMENT_DETAILS Team ON Team.DEPARTMENT_CODE = Employee.DEPTID
*
ERROR at line 4:
ORA-00904: "TEAM"."DEPARTMENT_CODE": invalid identifier
The DDL of one table is:
一张表的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
回答by APC
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 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:
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
tl;博士
don't use double quotes in DDL scripts
不要在 DDL 脚本中使用双引号
(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
… or
… 或者
select * from PS_TBL_DEPARTMENT_DETAILS;
… or
… 或者
select * from PS_Tbl_Department_Details
where COMAPNY_CODE = 'ORCL'
and cost_center_number = '0980'
回答by Sireesh Yarlagadda
In my case, this error occurred, due to lack of existence of column name in the table.
就我而言，由于表中不存在列名，因此发生了此错误。
When i executed "describe tablename" , i was not able to find the column specified in the mapping hbm file.
当我执行 " describe tablename" 时，我无法找到映射 hbm 文件中指定的列。
After altering the table, it worked fine.
更改表后，它工作正常。
回答by Datajam
DEPARTMENT_CODE is not a column that exists in the table Team. Check the DDL of the table to find the proper column name.
DEPARTMENT_CODE 不是表 Team 中存在的列。检查表的 DDL 以找到正确的列名。
回答by qyb2zm302
FYI, in this case the cause was found to be mixed case column name in the DDL for table creation.
仅供参考，在这种情况下，发现原因是 DDL 中用于表创建的列名混合大小写。
However, if you are mixing "old style" and ANSI joins you could get the same error message even when the DDL was done properly with uppercase table name. This happened to me, and google sent me to this stackoverflow page so I thought I'd share since I was here.
但是，如果您混合使用“旧样式”和 ANSI 连接，即使 DDL 使用大写表名正确完成，您也可能会收到相同的错误消息。这发生在我身上，谷歌将我发送到这个 stackoverflow 页面，所以我想我会在这里分享。
--NO PROBLEM: ANSI syntax
SELECT A.EMPLID, B.FIRST_NAME, C.LAST_NAME
FROM PS_PERSON A
INNER JOIN PS_NAME_PWD_VW B ON B.EMPLID = A.EMPLID
INNER JOIN PS_HCR_PERSON_NM_I C ON C.EMPLID = A.EMPLID
WHERE
LENGTH(A.EMPLID) = 9
AND LENGTH(B.LAST_NAME) > 5
AND LENGTH(C.LAST_NAME) > 5
ORDER BY 1, 2, 3
/
--NO PROBLEM: OLD STYLE/deprecated/traditional oracle proprietary join syntax
SELECT A.EMPLID, B.FIRST_NAME, C.LAST_NAME
FROM PS_PERSON A
, PS_NAME_PWD_VW B
, PS_HCR_PERSON_NM_I C
WHERE
B.EMPLID = A.EMPLID
and C.EMPLID = A.EMPLID
and LENGTH(A.EMPLID) = 9
AND LENGTH(B.LAST_NAME) > 5
AND LENGTH(C.LAST_NAME) > 5
ORDER BY 1, 2, 3
/
The two SQL statements above are equivalent and produce no error.
上面两条 SQL 语句是等价的，不会产生错误。
When you try to mix them you can get lucky, or you can get an Oracle has a ORA-00904 error.
当您尝试混合它们时，您可能会很幸运，或者您可能会遇到 Oracle 出现 ORA-00904 错误。
--LUCKY: mixed syntax (ANSI joins appear before OLD STYLE)
SELECT A.EMPLID, B.FIRST_NAME, C.LAST_NAME
FROM
PS_PERSON A
inner join PS_HCR_PERSON_NM_I C on C.EMPLID = A.EMPLID
, PS_NAME_PWD_VW B
WHERE
B.EMPLID = A.EMPLID
and LENGTH(A.EMPLID) = 9
AND LENGTH(B.FIRST_NAME) > 5
AND LENGTH(C.LAST_NAME) > 5
/
--PROBLEM: mixed syntax (OLD STYLE joins appear before ANSI)
--http://sqlfascination.com/2013/08/17/oracle-ansi-vs-old-style-joins/
SELECT A.EMPLID, B.FIRST_NAME, C.LAST_NAME
FROM
PS_PERSON A
, PS_NAME_PWD_VW B
inner join PS_HCR_PERSON_NM_I C on C.EMPLID = A.EMPLID
WHERE
B.EMPLID = A.EMPLID
and LENGTH(A.EMPLID) = 9
AND LENGTH(B.FIRST_NAME) > 5
AND LENGTH(C.LAST_NAME) > 5
/
And the unhelpful error message that doesn't really describe the problem at all:
以及根本没有真正描述问题的无用错误消息：
>[Error] Script lines: 1-12 -------------------------
ORA-00904: "A"."EMPLID": invalid identifier Script line 6, statement line 6,
column 51
I was able to find some research on this in the following blog post:
我能够在以下博客文章中找到一些关于此的研究：
In my case, I was attempting to manually convert from old style to ANSI style joins, and was doing so incrementally, one table at a time. This appears to have been a bad idea. Instead, it's probably better to convert all tables at once, or comment out a table and its where conditions in the original query in order to compare with the new ANSI query you are writing.
就我而言，我试图手动从旧样式转换为 ANSI 样式连接，并且以增量方式进行，一次一个表。这似乎是一个坏主意。相反，最好一次转换所有表，或者在原始查询中注释掉一个表及其 where 条件，以便与您正在编写的新 ANSI 查询进行比较。
回答by mcha
Are you sure you have a column DEPARTEMENT_CODEon your table PS_TBL_DEPARTMENT_DETAILS
你确定你有一列DEPARTEMENT_CODE你的桌子上PS_TBL_DEPARTMENT_DETAILS
More informationsabout your ERROR
有关您的 ERROR 的更多信息
ORA-00904: string: invalid identifier Cause: The column name entered is either missing or invalid. Action: Enter a valid column name. A valid column name must begin with a letter, be less than or equal to 30 characters, and consist of only alphanumeric characters and the special characters $, _, and #. If it contains other characters, then it must be enclosed in d double quotation marks. It may not be a reserved word.
ORA-00904：字符串：无效标识符原因：输入的列名要么丢失，要么无效。行动：输入一个有效的列名。有效的列名必须以字母开头，少于或等于 30 个字符，并且仅由字母数字字符和特殊字符 $、_ 和 # 组成。如果它包含其他字符，那么它必须用 d 双引号括起来。它可能不是保留字。
回答by C.L.S
I had the same exception in JPA 2 using eclipse link. I had an @embedded class with one to one relationship with an entity. By mistake ,in the embedded class, i had also the annotation @Table("TRADER"). When the DB was created by the JPA from the entities it also created a table TRADER (which was a wrong as the Trader entity was embedded to the main entity) and the existence of that table was causing the above exception every time i was trying to persist my entity. After deleting the TRADER table the exception disappered.
我在使用 eclipse 链接的 JPA 2 中遇到了同样的异常。我有一个与实体一对一关系的@embedded 类。错误地，在嵌入式类中，我也有注释@Table("TRADER")。当 JPA 从实体创建 DB 时，它还创建了一个表 TRADER（这是错误的，因为 Trader 实体嵌入到主实体中）并且该表的存在每次我尝试时都会导致上述异常坚持我的实体。删除 TRADER 表后，异常消失了。
回答by voccoeisuoi
Also make sure the user issuing the query has been granted the necessary permissions.
还要确保发出查询的用户已被授予必要的权限。
For queries on tables you need to grant SELECT permission.
For queries on other object types (e.g. stored procedures) you need to grant EXECUTE permission.
对于表查询，您需要授予 SELECT 权限。
对于其他对象类型（例如存储过程）的查询，您需要授予 EXECUTE 权限。
回答by Ashutosh Singh
I was passing the values without the quotes. Once I passed the conditions inside the single quotes worked like a charm.
我正在传递没有引号的值。一旦我通过了单引号内的条件，它就像一个魅力。
Select * from emp_table where emp_id=123;
instead of the above use this:
而不是上面使用这个：
Select * from emp_table where emp_id='123';
回答by alseddiq
I had this error when trying to save an entity through JPA.
尝试通过 JPA 保存实体时出现此错误。
It was because I had a column with @JoinColumnannotation that didn't have @ManyToOneannotation.
这是因为我有一个带有@JoinColumn注释的列，但没有@ManyToOne注释。
Adding @ManyToOnefixed the issue.
添加@ManyToOne修复了问题。
