Searching for help for PATSTAT query
Posted: 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
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);"