Help with search for ipc/cpc and then keyword

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

Help with search for ipc/cpc and then keyword

Post by waka » Thu Jul 07, 2022 6:26 pm

Hi, I'm trying, first, to do requests there are related to robotics and then make a search using keywords like robots, robotics, and industrial robots, can you guys help me with the "and then" part?

Code: Select all

SELECT distinct tls201_appln.docdb_family_id
	  ,tls201_appln.appln_id
      ,[appln_auth]
      ,[appln_nr]
      ,[appln_kind]
      ,[appln_filing_date]
      ,[receiving_office]
      ,[earliest_publn_date]
      ,[granted]
      ,[nb_citing_docdb_fam]
      ,[nb_applicants]
      ,[nb_inventors]
	 
FROM tls201_appln
WHERE (tls201_appln.appln_id in (select appln_id from tls209_appln_ipc where ipc_class_symbol  like 'B25J%')
or tls201_appln.appln_id in (select appln_id from tls224_appln_cpc where cpc_class_symbol  like 'B25J%') or tls201_appln.appln_id in (select appln_id from tls209_appln_ipc where ipc_class_symbol  like 'B60W%') or tls201_appln.appln_id in (select appln_id from tls224_appln_cpc where cpc_class_symbol  like 'B60W%') )
AND appln_filing_year between 2003 and 2008
AND granted = 'Y'
order by docdb_family_id, appln_filing_date asc
But after this, I don't know how to research keywords on the same query


Patent Information Marketing
Posts: 358
Joined: Fri Mar 03, 2017 1:16 pm

Re: Help with search for ipc/cpc and then keyword

Post by Patent Information Marketing » Thu Jul 07, 2022 7:00 pm

Dear user,
in order to search data from different tables you need to join them. Please see the introductory information on the product pages: https://www.epo.org/searching-for-paten ... tstat.html e.g. getting started.
I hope you will find this helpful.
Kind regards

Patent Information Marketing


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

Re: Help with search for ipc/cpc and then keyword

Post by waka » Fri Jul 08, 2022 8:04 pm

Patent Information Marketing wrote:
Thu Jul 07, 2022 7:00 pm
Dear user,
in order to search data from different tables you need to join them. Please see the introductory information on the product pages: https://www.epo.org/searching-for-paten ... tstat.html e.g. getting started.
I hope you will find this helpful.
sorry, I'm not good with SQL. i SHould create another table? like this

Code: Select all

SELECT distinct tls201_appln.docdb_family_id ,tls201_appln.appln_id ,[appln_auth] ,[appln_nr] ,[appln_kind] ,[appln_filing_date] ,[receiving_office] ,[earliest_publn_date] ,[granted] ,[nb_citing_docdb_fam] ,[nb_applicants] ,[nb_inventors] ,tls202_appln_title.appln_title 
 FROM tls201_appln INNER JOIN tls202_appln_title ON tls201_appln.appln_id = tls202_appln_title.appln_id INNER JOIN tls203_appln_abstr ON tls201_appln.appln_id = tls203_appln_abstr.appln_id 
 WHERE(appln_title like '%industrial robot%' or appln_abstract like '%industrial robot%') 
 AND appln_filing_year between 2003 and 2008 
 AND granted = 'Y' 
 order by docdb_family_id, appln_filing_date asc


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

Re: Help with search for ipc/cpc and then keyword

Post by EPO / PATSTAT Support » Thu Sep 01, 2022 11:12 pm

There is a specific CPC (and IPC) classification code for "industrial robots":
https://worldwide.espacenet.com/patent/ ... C=B25J9/00.
You can use that classification code "as-is" for your searches.
If you further limit the result by defining that " WHERE(appln_title like '%industrial robot%' or appln_abstract like '%industrial robot%') ", then you will narrow down the search based on what the patent applicant wanted to communicate what the patent was about, but not necessarily what the patent office decided what the patent claims define.
Below is a query that uses the IPC classification code in combination with your condition : WHERE(appln_title like '%industrial robot%' or appln_abstract like '%industrial robot%') in the condition-... which is restricting the results to about 5% of what the IPC classification codes define as being relevant to "industrial robots".

Code: Select all

SELECT distinct tls201_appln.docdb_family_id ,tls201_appln.appln_id ,[appln_auth] ,[appln_nr] ,[appln_kind] ,[appln_filing_date] ,
[receiving_office] ,[earliest_publn_date] ,[granted] ,[nb_citing_docdb_fam] ,[nb_applicants] ,[nb_inventors] ,
tls202_appln_title.appln_title
FROM tls201_appln INNER JOIN tls202_appln_title ON tls201_appln.appln_id = tls202_appln_title.appln_id 
INNER JOIN tls203_appln_abstr ON tls201_appln.appln_id = tls203_appln_abstr.appln_id 
WHERE (appln_title like '%industrial robot%' or appln_abstract like '%industrial robot%')  AND 
appln_filing_year between 2003 and 2008 
AND granted = 'Y' 
and tls201_appln.appln_id in (select appln_id from tls209_appln_ipc where left (ipc_class_symbol,8) like 'B25J   9%')
order by docdb_family_id, appln_filing_date asc
[code]
You might want to fine tune this to your needs.
PATSTAT Support Team
EPO - Vienna
patstat @ epo.org


Post Reply