Countries (Publication) Query

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

waka
Posts: 8
Joined: Wed Nov 10, 2021 5:00 pm

Countries (Publication) Query

Post by waka » Wed Mar 30, 2022 11:19 pm

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?


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

Re: Countries (Publication) Query

Post by EPO / PATSTAT Support » Thu Mar 31, 2022 1:28 pm

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.

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
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
PATSTAT Support Team
EPO - Vienna
patstat @ epo.org


waka
Posts: 8
Joined: Wed Nov 10, 2021 5:00 pm

Re: Countries (Publication) Query

Post by waka » Thu Mar 31, 2022 1:56 pm

EPO / PATSTAT Support wrote:
Thu Mar 31, 2022 1:28 pm
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.

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
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
Thanks. I tried Espacenet, but could not find the granted filter. Do you know where can I find it?


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

Re: Countries (Publication) Query

Post by EPO / PATSTAT Support » Thu May 05, 2022 11:44 am

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
PATSTAT Support Team
EPO - Vienna
patstat @ epo.org


Post Reply