CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloper
user3617496
user3617496

Reputation: 91

oracle sql developer: 00904. 00000 - "%s: invalid identifier". Where is my fault?

I am trying find my fault. I am getting this error message:

SQL-Fehler: ORA-00904: "S1"."PARTNO": ungültiger Bezeichner 00904. 00000 - "%s: invalid identifier"

I have checked my database and all tables exist.

Here is my sql code:

select s1.*
 , p.city as "Produktionsort" 
 , p.partname
from (select count(s.partno) as "Anzahl_Produktarten"
        , s.partno as "Partno" 
      from company.supp_part_job s 
      group by s.partno ) s1
 , company.part p 
where s1.partno IN (select p1.partno from company.part p1 where p1.city != 'London') 
   and p.partno = s1.partno 
group by s1.partno

Upvotes: 9

Views: 64913

Answers (2)

A.B.Cade
A.B.Cade

Reputation: 16905

Because you aliased in the inner select (s1) partno as "Partno" you must refer to it as case sensitive in the outer query:

select s1.*
 , p.city as "Produktionsort" 
 , p.partname
from (select count(s.partno) as "Anzahl_Produktarten"
        , s.partno as "Partno" 
      from company.supp_part_job s 
      group by s.partno ) s1
 , company.part p 
where s1."Partno" IN (select p1.partno from company.part p1 where p1.city != 'London') 
   and p.partno = s1."Partno" 
group by s1."Partno"

Upvotes: 5

Glenn
Glenn

Reputation: 9160

If you put double quotes around a column name, it will make it case sensitive. So I would think this line:

s.partno as "Partno"

is creating a case-sensitive s1."Partno" but the where clause is looking for s1.partno. Try removing the double quotes from your column aliases.

Upvotes: 2

Related Questions