奇怪的 Oracle SQL“无效标识符”错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7410627/
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
Weird Oracle SQL "Invalid Identifier" error
提问by JohnQPublic
Can anyone help me figure out why I get an error on cms.CRIME_ID:
任何人都可以帮我弄清楚为什么我会在以下位置出现错误cms.CRIME_ID：
invalid identifier
不合法的识别符
select c.criminal_id, c.first, c.last, cms.CRIME_ID, cc.crime_code, cc.fine_amount
from criminals c join crimes cms on c.criminal_id = cms.criminal_id
join crime_charges cc using (crime_id)
order by c.first, c.last;
I know for an absolute fact that column exists and I can reference every other column in that table except for that.
我知道列存在的绝对事实，除此之外，我可以引用该表中的所有其他列。
The only thing different about that column is that it is the primary key for that table.
该列唯一不同的是它是该表的主键。
EDIT: Here is the error in full and the table creation script.
编辑：这是完整的错误和表创建脚本。
Error starting at line 1 in command:
select c.criminal_id, c.first, c.last, cms.CRIME_ID, cc.crime_code, cc.fine_amount
from criminals c join crimes cms on c.criminal_id = cms.criminal_id
join crime_charges cc using (crime_id)
order by c.first, c.last
Error at Command Line:1 Column:39
Error report:
SQL Error: ORA-00904: "CMS"."CRIME_ID": invalid identifier
00904. 00000 - "%s: invalid identifier"
*Cause:
*Action:
CREATE TABLE crimes
(crime_id NUMBER(9),
criminal_id NUMBER(6),
classification CHAR(1),
date_charged DATE,
status CHAR(2),
hearing_date DATE,
appeal_cut_date DATE);
ALTER TABLE crimes
MODIFY (classification DEFAULT 'U');
ALTER TABLE crimes
ADD (date_recorded DATE DEFAULT SYSDATE);
ALTER TABLE crimes
MODIFY (criminal_id NOT NULL);
ALTER TABLE crimes
ADD CONSTRAINT crimes_id_pk PRIMARY KEY (crime_id);
ALTER TABLE crimes
ADD CONSTRAINT crimes_class_ck CHECK (classification IN('F','M','O','U'));
ALTER TABLE crimes
ADD CONSTRAINT crimes_status_ck CHECK (status IN('CL','CA','IA'));
ALTER TABLE crimes
ADD CONSTRAINT crimes_criminalid_fk FOREIGN KEY (criminal_id)
REFERENCES criminals(criminal_id);
ALTER TABLE crimes
MODIFY (criminal_id NOT NULL);
EDIT2: Also, I should probably mention that when not using joins and just regular select statements I can access the column just fine, as in the following code example:
EDIT2：另外，我可能应该提到，当不使用连接和只使用常规选择语句时，我可以很好地访问该列，如下面的代码示例所示：
select c.criminal_id, c.first, c.last, cms.crime_id, cc.crime_code, cc.fine_amount
from criminals c, crime_charges cc, crimes cms
where c.criminal_id = cms.criminal_id
and cms.crime_id = cc.crime_id
order by c.first, c.last;
回答by Luke Woodward
The problem here is that when your query has a USINGclause, you can't add qualifiers to the column(s) used within this clause. Because your query has USING (crime_id),you can't write cms.CRIME_IDnor cc.crime_id. Instead, you must remove the qualifier, i.e., just use crime_id.
这里的问题是，当您的查询有一个USING子句时，您不能向该子句中使用的列添加限定符。因为你的查询有USING (crime_id),你不能写cms.CRIME_ID也不能cc.crime_id。相反，您必须删除限定符，即只使用crime_id.
Oddly enough, when I try this on Oracle 11g XE beta, I get a different error:
奇怪的是，当我在 Oracle 11g XE beta 上尝试这个时，我得到了一个不同的错误：
SQL> select * from test1;
A B
---------- ----------
1 2
SQL> select * from test2;
A C
---------- ----------
1 3
SQL> select t1.a, t1.b, t2.c from test1 t1 inner join test2 t2 using (a);
select t1.a, t1.b, t2.c from test1 t1 inner join test2 t2 using (a)
*
ERROR at line 1:
ORA-25154: column part of USING clause cannot have qualifier
SQL> select a, t1.b, t2.c from test1 t1 inner join test2 t2 using (a);
A B C
---------- ---------- ----------
1 2 3
回答by pravs
you cannot use qualifier with column referenced by "using" clause.you could use inner join instead of that try using this query:
您不能将限定符与“using”子句引用的列一起使用。您可以使用内部连接而不是尝试使用以下查询：
select c.criminal_id, c.first, c.last, cms.CRIME_ID, cc.crime_code, cc.fine_amount
from criminals c join crimes cms on c.criminal_id = cms.criminal_id
join crime_charges cc on cc.crime_id=cms.crime_id
order by c.first, c.last;
回答by Khilitchandra Prajapati
Did you try the following ?
您是否尝试过以下操作？
Join On (Left Id) = (Right Id)
Instead of keyword Using
而不是关键字 Using
回答by Thinhbk
try to make an alias for join statements clause:
尝试为 join statements 子句创建别名：
select alias.criminal_id, alias.first, alias.last, alias.CRIME_ID, alias.crime_code, alias.fine_amount
from criminals c join crimes cms on c.criminal_id = cms.criminal_id
join crime_charges cc using (crime_id) as alias
order by alias.first, alias.last;
