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
Thank you so much for your help.
Best regards
Elisa