Error Weird Oracle SQL "Invalid identifier"

Question

Can someone help me figure out why I am getting the cms.CRIME_ID error cms.CRIME_ID :

invalid id

 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 that it is an absolute fact that a column exists, and I can refer to every column in this table except this one.

The only difference in this column is that it is the primary key for this table.

EDIT: complete error and 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 you don't use joins and just plain select statements, I can access the column just fine, as in the following code example:

 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; 

Answer 1

The problem is that when your query has a USING , you cannot add qualifiers to the column (s) used in this section. Since your request has USING (crime_id), , you cannot write cms.CRIME_ID and cc.crime_id . Instead, you should remove the qualifier, i.e. Just use crime_id .

Oddly enough, when I try this on the beta version of Oracle 11g XE, I get another error:

  SQL> select * from test1;

          Ab
 ---------- ----------
          12

 SQL> select * from test2;

          AC
 ---------- ----------
          thirteen

 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);

          Abc
 ---------- ---------- ----------
          1 2 3

Answer 2

you cannot use the qualifier with the column referenced by the using clause. You can use the inner join instead, try using this query:

 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; 

Answer 3

Have you tried the following?

 Join On (Left Id) = (Right Id) 

Instead of Using keyword

Answer 4

try making an alias to suggest 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;