You got the error message "invalid column name 'appln_id' because "c.appln_id" (which is your alias for "tls226_person_orig.appln_id" does not exist: There is no attribute APPLN_ID in the table TLS226_PERSON_ORIG.
I suggest to check the PATSTAT Data Catalog on
http://epo.org/patstat. In the graphical data model in section 3 you see that to combine applications (in table TLS201_APPLN) and persons (in TLS206_PERSON or TLS906_PERSON) you must go via the table TLS207_PERS_APPLN. (Note: Using TLS206/TLS906 is easier than TLS226_PERSON_ORIG. This is usually sufficient, unless you want to dive into the depths of name & address analysis.)
Usually you join 2 tables - like you did - via their common attributes. More generally and correctly, you join tables via the foreign key (FK) as indicated in the diagram.
The query you are looking for might be this:
Code: Select all
select *
from tls224_appln_cpc a
join tls201_appln b on a.appln_id = b.appln_id
join tls207_pers_appln pa on a.appln_id = pa.appln_id
join tls206_person p on pa.person_id = p.person_id
where cpc_class_symbol like 'Y02%'
order by a.appln_id
It retrieves more than 19 million rows in the PATSTAT 2018 Autumn Edition, because your query is quite general. So the query execution will take some minutes.
Note that an application might be represented by multiple rows, because each row represents a combination of application x IPC code Y02.. x persons (applicants and inventors).
As you are new to PATSTAT, working to the self study guide "Using PATSTAT with SQL for beginners" might be a good way to start.
Have success,
Martin / EPO PATSTAT