I would like to retrieve the number of oppositions in all the IPC-4 digit classes. I don't know if there is a way to avoid explicitly listing in the query all the IPC-4 digit, but make it automatic. If not, how could I structure my query? I don't mind if a single patent that pertains to multiple IPCs is counted as many times as its specifications.
Thank you so much for your precious help.
Best regards
Elisa
Edit: I tryed with the following query
Code: Select all
SELECT LEFT(aipc.ipc_class_symbol, 4), COUNT(a.appln_id) AS 'opp_count'
FROM tls201_appln a
join tls211_pat_publn pp on a.appln_id = pp.appln_id
join tls209_appln_ipc aipc on a.appln_id = aipc.appln_id
join tls207_pers_appln pa on a.appln_id = pa.appln_id
join tls206_person pe on pa.person_id = pe.person_id
join reg101_appln re on a.appln_id = re.appln_id
join reg130_opponent op on re.id = op.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 pe.psn_sector = 'COMPANY'
AND pp.publn_kind = 'B1'
AND op.oppt_name not like 'NULL'
AND len(aipc.ipc_class_symbol) >= 4
GROUP BY aipc.ipc_class_symbol
ORDER BY opp_count DESC
Thank you so much,
Elisa