distinguish inventor country and applicant country

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

leonie90
Posts: 2
Joined: Thu Aug 17, 2017 4:26 pm

distinguish inventor country and applicant country

Post by leonie90 » Thu Aug 17, 2017 4:40 pm

Dear community,

I'm working on a PATSTAT Online (Spring 2017) patent query.
My aim is to retrieve all patent application for 2000-2015 for a sample of European Countries. I want do distinguish between Inventor Country and Applicant Country. (I am replicating another Study for my masterthesis. I retrieve too much applications for this period 2000-2015)

Here my query that i use for search for all patent applications for some European (Applicant) countries for the years 2000-2015.

SELECT distinct * 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
WHERE (appln_nr_epodoc Like '%AT%' OR appln_nr_epodoc Like '%BG%' OR appln_nr_epodoc Like '%HR%' OR appln_nr_epodoc Like '%CZ%' OR appln_nr_epodoc Like '%DK%' OR appln_nr_epodoc Like '%EE%' OR appln_nr_epodoc Like '%FI%' OR appln_nr_epodoc Like '%DE%' OR appln_nr_epodoc Like '%GR%' OR appln_nr_epodoc Like '%IS%' OR appln_nr_epodoc Like '%LA%' OR appln_nr_epodoc Like '%LV%' OR appln_nr_epodoc Like '%NO%' OR appln_nr_epodoc='PT' OR appln_nr_epodoc Like '%IT%' or appln_nr_epodoc Like '%RO%' OR appln_nr_epodoc Like '%SK%' OR appln_nr_epodoc Like '%SI%' OR appln_nr_epodoc Like '%PL%')
AND tls201_appln.appln_filing_year>=2015 AND tls201_appln.appln_filing_year>=2000 AND tls207_pers_appln.applt_seq_nr=1 AND tls207_pers_appln.invt_seq_nr=0 AND tls206_person.psn_sector='COMPANY'




What could be wrong with this Query? :shock: :shock: :shock: :shock: :shock:

Thank you a lot in advance, Leo


Geert Boedt
Posts: 176
Joined: Tue Oct 19, 2004 10:36 am
Location: Vienna

Re: distinguish inventor country and applicant country

Post by Geert Boedt » Fri Sep 01, 2017 4:31 pm

Hello Leo,
your query has a couple of syntax errors, but in general, I don't see the meaning of using the appln_nr_epodoc the way it is done in your query. The attribute appln_nr_epodoc has no relation to the country of inventor or applicants. The country code in appln_nr_epodoc will normally be the same as the appln_auth code.
If you want to limit to applicants from certain counrtries, then you should add a condition on the person country code.
Here is a reworked example query:

Code: Select all

SELECT distinct appln_auth, appln_nr, appln_kind, appln_filing_date,applt_seq_nr,psn_name, person_ctry_code 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
 WHERE appln_auth in ('at', 'cz')
 AND tls201_appln.appln_filing_year between 2000 and 2002
 AND tls207_pers_appln.applt_seq_nr > 0 
 AND tls207_pers_appln.invt_seq_nr  = 0 
 AND tls206_person.psn_sector='COMPANY'
 and person_ctry_code = 'US'
Best regards,

Geert Boedt
PATSTAT support
Business Use of Patent Information
EPO Vienna


leonie90
Posts: 2
Joined: Thu Aug 17, 2017 4:26 pm

Re: distinguish inventor country and applicant country

Post by leonie90 » Wed Sep 13, 2017 9:24 am

Thanks a lot!
Do you have any idea how I could filter out just the Inventors, who are also applicants?

I think about a condition in the WHERE clause like:

"applt_seq_nr>0 AND invt_seq_nr>0"

Thank you a lot in advance, Leo


Geert Boedt
Posts: 176
Joined: Tue Oct 19, 2004 10:36 am
Location: Vienna

Re: distinguish inventor country and applicant country

Post by Geert Boedt » Wed Sep 13, 2017 11:20 am

Correct, don't forget the NOT (If you want to filter them out).
Otherwise you will only have inventors that are applicants.

You could also use "applt_seq_nr>0 AND invt_seq_nr = 0" to filter out many of the natural persons from the applicants. This might be particular useful when looking at US data where initially inventors are default applicant.
Best regards,

Geert Boedt
PATSTAT support
Business Use of Patent Information
EPO Vienna


Post Reply