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

Question

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

Answer 1

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"

Answer 2

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.