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.
-
waka
- Posts: 8
- Joined: Wed Nov 10, 2021 5:00 pm
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
-
waka
- Posts: 8
- Joined: Wed Nov 10, 2021 5:00 pm
Post
by waka » Fri Jul 08, 2022 8:04 pm
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: 440
- Joined: Thu Feb 22, 2007 5:33 pm
-
Contact:
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