Countries (Publication) Query
Countries (Publication) Query
Hi guys, how are you? I'm looking for coffee patents deposited in Mexico between 1990 and 2020, but I don't know if the command appln_auth = 'MX' is about patents filed in MX or patents made by MX. Can you guys give me some help?
-
- Posts: 440
- Joined: Thu Feb 22, 2007 5:33 pm
- Contact:
Re: Countries (Publication) Query
Hello Waka,
a condition such as appln_auth = 'MX' will give only patents that were filed in Mexico -most of them in fact not filed by Mexican applicants, but rather large multinationals that want to protect their inventions in Mexico-. If you want to only retrieve patents filed in Mexico by Mexican applicants, then you will need to add an extra condition such as " person_ctry_code = 'MX' "
"Coffee patents" is a rather vague definition, but you could use an IPC or CPC code being = 'A23F5' , then you will find "coffee related" patents.
Below is a sample SQL query using IPC & CPC codes. You can copy this in PATSTAT Online an download the results.
Working with PATSTAT requires some SQL and RD DB's skills. Espacenet is maybe a valid alternative for your data needs.
Something like this: https://worldwide.espacenet.com/patent/ ... 0%22MX%22
a condition such as appln_auth = 'MX' will give only patents that were filed in Mexico -most of them in fact not filed by Mexican applicants, but rather large multinationals that want to protect their inventions in Mexico-. If you want to only retrieve patents filed in Mexico by Mexican applicants, then you will need to add an extra condition such as " person_ctry_code = 'MX' "
"Coffee patents" is a rather vague definition, but you could use an IPC or CPC code being = 'A23F5' , then you will find "coffee related" patents.
Below is a sample SQL query using IPC & CPC codes. You can copy this in PATSTAT Online an download the results.
Code: Select all
SELECT distinct tls201_appln.appln_id
,publn_auth
,publn_nr
,publn_kind
,publn_date
,appln_filing_date
,appln_nr_original
,earliest_filing_date
,granted
, psn_name
,person_ctry_code
,appln_title
FROM tls201_appln
join tls211_pat_publn on earliest_pat_publn_id = tls211_pat_publn.pat_publn_id
left join tls209_appln_ipc on tls201_appln.appln_id = tls209_appln_ipc.appln_id
left join tls224_appln_cpc on tls201_appln.appln_id = tls224_appln_cpc.appln_id
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 and applt_seq_nr = 1
left join tls202_appln_title on tls201_appln.appln_id = tls202_appln_title.appln_id
where appln_auth = 'MX'
and (left(ipc_class_symbol,8) = 'A23F 5' or left(cpc_class_symbol,8) = 'A23F 5')
--and person_ctry_code = 'MX'
and tls201_appln.appln_id < 900000000
order by appln_filing_date desc
Something like this: https://worldwide.espacenet.com/patent/ ... 0%22MX%22
PATSTAT Support Team
EPO - Vienna
patstat @ epo.org
EPO - Vienna
patstat @ epo.org
Re: Countries (Publication) Query
Thanks. I tried Espacenet, but could not find the granted filter. Do you know where can I find it?EPO / PATSTAT Support wrote: ↑Thu Mar 31, 2022 1:28 pmHello Waka,
a condition such as appln_auth = 'MX' will give only patents that were filed in Mexico -most of them in fact not filed by Mexican applicants, but rather large multinationals that want to protect their inventions in Mexico-. If you want to only retrieve patents filed in Mexico by Mexican applicants, then you will need to add an extra condition such as " person_ctry_code = 'MX' "
"Coffee patents" is a rather vague definition, but you could use an IPC or CPC code being = 'A23F5' , then you will find "coffee related" patents.
Below is a sample SQL query using IPC & CPC codes. You can copy this in PATSTAT Online an download the results.
Working with PATSTAT requires some SQL and RD DB's skills. Espacenet is maybe a valid alternative for your data needs.Code: Select all
SELECT distinct tls201_appln.appln_id ,publn_auth ,publn_nr ,publn_kind ,publn_date ,appln_filing_date ,appln_nr_original ,earliest_filing_date ,granted , psn_name ,person_ctry_code ,appln_title FROM tls201_appln join tls211_pat_publn on earliest_pat_publn_id = tls211_pat_publn.pat_publn_id left join tls209_appln_ipc on tls201_appln.appln_id = tls209_appln_ipc.appln_id left join tls224_appln_cpc on tls201_appln.appln_id = tls224_appln_cpc.appln_id 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 and applt_seq_nr = 1 left join tls202_appln_title on tls201_appln.appln_id = tls202_appln_title.appln_id where appln_auth = 'MX' and (left(ipc_class_symbol,8) = 'A23F 5' or left(cpc_class_symbol,8) = 'A23F 5') --and person_ctry_code = 'MX' and tls201_appln.appln_id < 900000000 order by appln_filing_date desc
Something like this: https://worldwide.espacenet.com/patent/ ... 0%22MX%22
-
- Posts: 440
- Joined: Thu Feb 22, 2007 5:33 pm
- Contact:
Re: Countries (Publication) Query
ESPACENET does not have a search field or filter to only select granted patent applications across all patent offices.
A "workaround" for some patent authorities is to add the publication kind code for the publication of the granted patent in the publication number field.
Something like this (for US and EP)
https://worldwide.espacenet.com/patent/ ... %22usb%22
A "workaround" for some patent authorities is to add the publication kind code for the publication of the granted patent in the publication number field.
Something like this (for US and EP)
https://worldwide.espacenet.com/patent/ ... %22usb%22
PATSTAT Support Team
EPO - Vienna
patstat @ epo.org
EPO - Vienna
patstat @ epo.org