Research on opposition

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

Elisa_D
Posts: 13
Joined: Wed Jan 26, 2022 10:38 am

Research on opposition

Post by Elisa_D » Wed Aug 03, 2022 4:05 pm

Dear all,
I am currently trying to retrive all the patent opposition occured in a specific IPC class. I am not sure about the results I obtain also because i fear data are incomplete.
Specifically, I am trying to see the opposition received by patent filed for cosmetics (IPC=A61Q); i don't mind if i have multiple line for the same patent as there are more than one opposition filed by different opponent.
The query I wrote is the following

Code: Select all

SELECT DISTINCT a.appln_id, a.appln_auth, a.appln_filing_date, a.appln_filing_year, a.earliest_publn_date, a.docdb_family_size, a.nb_applicants, op.oppt_name 
 FROM tls201_appln a
 full outer join reg101_appln ra on a.appln_id = ra.appln_id
 full outer join reg130_opponent op on ra.id = op.id
 full outer join tls209_appln_ipc aipc on a.appln_id = aipc.appln_id 
 WHERE
 a.appln_kind = 'A' -- Patent (exclude the PCT applications that have not yet entered reg. phase.)
 AND  a.appln_id < 900000000 -- exclude artificial applications
 AND a.ipr_type = 'PI'
 AND a.granted = 'Y'
 AND a.appln_auth = 'EP'
 AND aipc.ipc_class_symbol LIKE 'A61Q%' 
 GROUP BY a.appln_id, a.appln_auth, a.appln_filing_date, a.appln_filing_year, a.earliest_publn_date, a.docdb_family_size, a.nb_applicants, op.oppt_name
With this query, since I used the "full outer join" function, I expect to find the complete list of patent application recived and granted by EPO (without time constraint) for the cosmetics, characterized by a "NULL" value under "oppt_name" column when the patent had no opposition. Am I right?

Thank you so much for your help.
Best regards
Elisa


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

Re: Research on opposition

Post by EPO / PATSTAT Support » Thu Aug 04, 2022 7:53 am

Hello Elisa,
that looks fine to me.

It is maybe useful to add an extra line on your query that sorts the result according to application and application filing date. That will allow you to also easily identify those patent applications for which there were more then one opponent. Each new extra opponent will indeed have a new row in the result.

Code: Select all

SELECT DISTINCT a.appln_id, a.appln_auth, a.appln_filing_date, a.appln_filing_year, a.earliest_publn_date, a.docdb_family_size, a.nb_applicants, op.oppt_name 
 FROM tls201_appln a
 full outer join reg101_appln ra on a.appln_id = ra.appln_id
 full outer join reg130_opponent op on ra.id = op.id
 full outer join tls209_appln_ipc aipc on a.appln_id = aipc.appln_id 
 WHERE
 a.appln_kind = 'A' -- Patent (exclude the PCT applications that have not yet entered reg. phase.)
 AND  a.appln_id < 900000000 -- exclude artificial applications
 AND a.ipr_type = 'PI'
 AND a.granted = 'Y'
 AND a.appln_auth = 'EP'
 AND aipc.ipc_class_symbol LIKE 'A61Q%' 
 GROUP BY a.appln_id, a.appln_auth, a.appln_filing_date, a.appln_filing_year, a.earliest_publn_date, a.docdb_family_size, a.nb_applicants, op.oppt_name
 order by appln_filing_date desc, appln_id
PATSTAT Support Team
EPO - Vienna
patstat @ epo.org


Post Reply