Page 1 of 1

Searching for help for PATSTAT query

Posted: Sat Mar 13, 2021 6:46 pm
by Hannover student
Hello everyone,

concerning my master thesis I wish to retreive patent data (inventors/company addresses - NUTS region and cpc classes of patents) for Germany in the time frame of 2010-2020.

Therefore, I found a query which did the same for one region ("Niedersachsen").

I modified the query, in order to apply for the whole country. Sadly, I just get error-terms.

Could you help me adjusting the query code?

Best regards,

Hannover student

Code: Select all

query_LSTech <- "CREATE VIEW LSTech_i
AS SELECT DISTINCT TLS207_PERS_APPLN.APPLN_ID FROM TLS207_PERS_APPLN 
INNER JOIN TLS206_PERSON ON TLS207_PERS_APPLN.PERSON_ID = TLS206_PERSON.PERSON_ID 
INNER JOIN TLS201_APPLN ON TLS207_PERS_APPLN.APPLN_ID = TLS201_APPLN.APPLN_ID 
INNER JOIN TLS906_PERSON ON TLS207_PERS_APPLN.PERSON_ID=TLS906_PERSON.PERSON_ID
WHERE (TLS207_PERS_APPLN.INVT_SEQ_NR > 0) AND TLS201_APPLN.EARLIEST_FILING_YEAR > 2009 AND TLS201_APPLN.EARLIEST_FILING_YEAR < 2021 AND TLS906_PERSON.NUTS LIKE 'DE%';"

query_cpc <- "SELECT DISTINCT TLS201_APPLN.APPLN_KIND, TLS201_APPLN.APPLN_ID, TLS201_APPLN.APPLN_NR, TLS201_APPLN.APPLN_AUTH, TLS224_APPLN_CPC.CPC_CLASS_SYMBOL, TLS201_APPLN.APPLN_NR_ORIGINAL, TLS201_APPLN.EARLIEST_FILING_DATE 
FROM LSTech_i G 
INNER JOIN TLS207_PERS_APPLN ON G.APPLN_ID = TLS207_PERS_APPLN.APPLN_ID 
INNER JOIN TLS206_PERSON ON TLS207_PERS_APPLN.PERSON_ID = TLS206_PERSON.PERSON_ID 
INNER JOIN TLS201_APPLN ON G.APPLN_ID = TLS201_APPLN.APPLN_ID 
INNER JOIN TLS224_APPLN_CPC ON G.APPLN_ID = TLS224_APPLN_CPC.APPLN_ID;"

query_loc <- "SELECT DISTINCT A.APPLN_ID, B.NUTS, B.HAN_NAME, B.PERSON_CTRY_CODE, B.PERSON_ADDRESS, B.NUTS, O.CITY, O.ZIP_CODE, O.STATE 
FROM LSTech_i G 
INNER JOIN TLS207_PERS_APPLN A ON G.APPLN_ID=A.APPLN_ID 
INNER JOIN TLS906_PERSON B ON A.PERSON_ID=B.PERSON_ID 
INNER JOIN TLS226_PERSON_ORIG O ON B.PERSON_ID=O.PERSON_ID WHERE (A.INVT_SEQ_NR > 0);"

Re: Searching for help for PATSTAT query

Posted: Wed Mar 17, 2021 2:55 pm
by EPO / PATSTAT Support
Hello Hannover student,
the table tls906 is deprecated, you should use tls206_person. (or tls226)
Check the data catalog relevant for your PATSTAT release under the documentation TAB here:
https://www.epo.org/searching-for-paten ... tstat.html
Also keep in mind that you can not create views on PATSTAT Online and that addresses have been removed for applicants and inventors that were identified as natural persons.

Code: Select all

CREATE VIEW LSTech_i
AS SELECT DISTINCT TLS207_PERS_APPLN.APPLN_ID 
FROM TLS207_PERS_APPLN 
INNER JOIN TLS206_PERSON ON TLS207_PERS_APPLN.PERSON_ID = TLS206_PERSON.PERSON_ID 
INNER JOIN TLS201_APPLN ON TLS207_PERS_APPLN.APPLN_ID = TLS201_APPLN.APPLN_ID 
WHERE (TLS207_PERS_APPLN.INVT_SEQ_NR > 0) 
AND TLS201_APPLN.EARLIEST_FILING_YEAR > 2009 
AND TLS201_APPLN.EARLIEST_FILING_YEAR < 2021 
AND TLS206_PERSON.NUTS LIKE 'DE%';