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?
Thank you a lot in advance, Leo
distinguish inventor country and applicant country
-
- Posts: 176
- Joined: Tue Oct 19, 2004 10:36 am
- Location: Vienna
Re: distinguish inventor country and applicant country
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:
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
Geert Boedt
PATSTAT support
Business Use of Patent Information
EPO Vienna
Re: distinguish inventor country and applicant country
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
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
-
- Posts: 176
- Joined: Tue Oct 19, 2004 10:36 am
- Location: Vienna
Re: distinguish inventor country and applicant country
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.
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
Geert Boedt
PATSTAT support
Business Use of Patent Information
EPO Vienna