Retrieving patents with inventors spread across multiple countries

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

rooster25
Posts: 1
Joined: Sat Mar 03, 2018 11:25 am

Retrieving patents with inventors spread across multiple countries

Post by rooster25 » Sat Mar 03, 2018 11:34 am

I am trying to retrieve patents that have more than one inventor and the inventors are situated in different countries.
I have written this:
CREATE VIEW nt AS
[SELECT tls201_appln.appln_id, tls201_appln.appln_filing_year, tls206_person.psn_id, tls206_person.person_ctry_code
FROM
tls201_appln,tls206_person, tls207_pers_appln
WHERE
tls201_appln.appln_id = tls207_pers_appln.appln_id
AND
tls207_pers_appln.person_id = tls206_person.person_id
AND
tls201_appln.appln_filing_date BETWEEN '1985' AND '1986'
AND tls201_appln.nb_inventors >= '2'];
SELECT DISTINCT appln_id, person_ctry_code
FROM nt
ORDER BY appln_id

but this is an error I am getting for the create view section:
[Error Code: 103, SQL State: S0004] The identifier that starts with 'SELECT tls201_appln.appln_id, tls201_appln.appln_filing_year, tls206_person.* FROM tls201_appln,tls206_person WHERE tls201_app' is too long. Maximum length is 128.
I seem to be unable to create a temporary table as well to carry out the required task.
(I am a novice, it is highly probable I am overlooking something obvious.) Any inputs on how to navigate this?
Thanks.


mkracker
Posts: 120
Joined: Wed Sep 04, 2013 6:17 am
Location: Vienna

Re: Retrieving patents with inventors spread across multiple countries

Post by mkracker » Mon Mar 05, 2018 2:57 pm

PATSTAT Online is a read-only database. You cannot create your own database views (CREATE VIEW ...). To create views, you have to set up your own local database and import PATSTAT data from its CSV files.

But in almost all cases you can just work around database views:
Here is a query which returns the same rows as yours would, but it is a nested query and does not require a database view. (Note: It returns all applications filed in 1985 and 1986; I am not sure if you wanted both years or just 1985.)

Code: Select all

SELECT  DISTINCT appln_id, person_ctry_code
FROM 
	(SELECT tls201_appln.appln_id, tls201_appln.appln_filing_year, tls206_person.psn_id, tls206_person.person_ctry_code
 	FROM  tls201_appln,tls206_person, tls207_pers_appln
 	WHERE  tls201_appln.appln_id = tls207_pers_appln.appln_id
 	AND  tls207_pers_appln.person_id = tls206_person.person_id
 	AND  tls201_appln.appln_filing_year BETWEEN 1985 AND 1986
 	AND tls201_appln.nb_inventors >= '2') nt
 ORDER BY appln_id
Actually, in your case a nested query is not even needed. It can be done even simpler:

Code: Select all

SELECT  DISTINCT tls201_appln.appln_id, person_ctry_code
 	FROM  tls201_appln,tls206_person, tls207_pers_appln
 	WHERE  tls201_appln.appln_id = tls207_pers_appln.appln_id
 	AND  tls207_pers_appln.person_id = tls206_person.person_id
 	AND  tls201_appln.appln_filing_year BETWEEN 1985 AND 1986
 	AND tls201_appln.nb_inventors >= 2
 ORDER BY tls201_appln.appln_id
But actually, you said that you only wanted applications with multiple inventors which are located in different countries. Your query will not return this information. Instead, I suggest to have a look at sample query 2c) in the document "Sample queries and tips for PATSTAT", which is accessible in tab "Documentation" of www.epo.org/patstat . The example retrieves applicants in different countries ( = international cooperations between applicants). It should be easy to adapt it to your needs by replacing applicants by inventors.

Hope that helps,
Martin
-------------------------------------------
Martin Kracker / EPO


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

Re: Retrieving patents with inventors spread across multiple countries

Post by EPO / PATSTAT Support » Mon Mar 05, 2018 5:30 pm

Hello Rooster25,
although it might look like a simple question, doing such aggregation in PATSTAT Online is not straightforward, and the only possibility is to use a sub query that defines applications that have more then 2 inventors from different countries (not being blank neither).
On a local installed PATSTAT database, I would first make an intermediate table that stores all the application id's that have more then 2 inventors from different countries. But I PATSTAT Online you can not create tables or views.

Here is the code that works. One important observation: if you run this query without any restricition on the full PATSTAT data base, it not work because of the enormous task of looking up all application in the sub query. I have therefore added a limitation to applications filed at the EPO on '2016-01-05' just to illustrate the concept. You will need to specifiy your own criteria, and add maybe further tables (IPC,CPC) to narrow down the sample. Observe that I have copied the same limitations in the subsquery as in the outer query. (in the hope that it reduces computational efforts)

Code: Select all

SELECT tls201_appln.appln_id, appln_auth,appln_nr, appln_kind, appln_filing_date,applt_seq_nr, invt_seq_nr, tls206_person.person_id,person_name, psn_name, person_address ,person_ctry_code, MORE_THEN_2.total
  FROM tls201_appln 
  join tls207_pers_appln on tls201_appln.appln_id = tls207_pers_appln.appln_id
  join tls206_person on tls207_pers_appln.person_id = tls206_person.person_id
--START SUBQUERY
JOIN (select tls207_pers_appln.appln_id , count(distinct(person_ctry_code)) total 
			from tls207_pers_appln join tls206_person on tls207_pers_appln.person_id = tls206_person.person_id
			join tls201_appln on tls201_appln.appln_id = tls207_pers_appln.appln_id
				where person_ctry_code <> '' and invt_seq_nr > 0 and tls201_appln.nb_inventors > 1  and appln_auth = 'EP' and appln_filing_date = '2016-01-25'
				group by tls207_pers_appln.appln_id 
				having count(distinct(person_ctry_code)) > 1 )  MORE_THEN_2
--END SUBQUERY
		ON tls201_appln.appln_id =  MORE_THEN_2.appln_id
where invt_seq_nr > 0 and appln_auth = 'EP' and appln_filing_date = '2016-01-25'
order by TLS201_APPLN.APPLN_ID, invt_seq_nr
Maybe somebody has a better more elegant approach ?
Geert BOEDT
PATSTAT Support Team
EPO - Vienna
patstat @ epo.org


Djay
Posts: 5
Joined: Tue Mar 12, 2019 8:26 am

Re: Retrieving patents with inventors spread across multiple countries

Post by Djay » Tue Mar 12, 2019 8:44 am

Hi PATSTAT Team,

I'm totally new to PATSTAT and SQL language. I'm trying to create a dataset for bilateral patent flows. So I need the number of patents for each country pair.

I have gone through Martin's code on retrieving patents spread across multiple countries. I'm only interested in patents with multiple inventors listed in different countries. How can I modify Martin's code for this?

Also is it possible to aggregate the data such that the system counts the number of patents for each country pair?

Appreciate if someone can help to modify the following code by Martin:

SELECT DISTINCT tls201_appln.appln_id, person_ctry_code
FROM tls201_appln,tls206_person, tls207_pers_appln
WHERE tls201_appln.appln_id = tls207_pers_appln.appln_id
AND tls207_pers_appln.person_id = tls206_person.person_id
AND tls201_appln.appln_filing_year BETWEEN 2000 AND 2005
AND tls201_appln.nb_inventors >= 2
ORDER BY tls201_appln.appln_id

Thank you very much.


Post Reply