Searching for help for PATSTAT query

Here you can post your opinions, ask questions and share experiences on the PATSTAT product line. Please always indicate the PATSTAT edition (e.g. 2015 Autumn Edition) and the database (e.g. PATSTAT Online, MySQL, MS SQL Server, ...) you are using.
Post Reply

Hannover student
Posts: 2
Joined: Sat Mar 13, 2021 6:23 pm

Searching for help for PATSTAT query

Post by Hannover student » Sat Mar 13, 2021 6:46 pm

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


EPO / PATSTAT Support
Posts: 425
Joined: Thu Feb 22, 2007 5:33 pm
Contact:

Re: Searching for help for PATSTAT query

Post by EPO / PATSTAT Support » Wed Mar 17, 2021 2:55 pm

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%';
PATSTAT Support Team
EPO - Vienna
patstat @ epo.org


Post Reply