I am still working on my research on opposition and I need to retrive all the opposition filed for cosmetic patents, without time restriction.
I have two issue:
1- I want to see as a single opposition (i.e. in a single row) the ones filled by more than one applicant, with a kind of "string_agg" for the name of opponets;
2- I want to see the outcome of the closed opposition (opposition withdrawn, opposition rejected, patent amended, patent revoked or opposition closed).
Regarding the point n.1:
Suppose I have a list of patent, identified by their application number (appln_nr). As we know, a patent can be granted to multiple applicants, identified by their "psn_name"= compan1, company2... and so on. For each patent, I can also have multiple opposition, noted as OPPOSITION1, OPPOSITION2 and so on; for each of these, we can have more than one opponet, noted as opponentA, opponentB, opponentC.
According to these specification, my ideal result table would be structured in the following way:
APPLN_NR-------------PSN_NAME (applicant)----------------OPPT_NAME-------
PATENT 1---------------company1;company2------------OPPOSITION 1: opponentA;opponentB
PATENT 1---------------company1;company2------------OPPOSITION 2: opponentC;opponentD;opponentE
PATENT 1---------------company1;company2------------OPPOSITION 3: opponentF
PATENT 2-------------------company3---------------------OPPOSITION 1: opponentG
PATENT 3-------------------company4---------------------OPPOSITION 1: opponentH; opponentI
PATENT 3-------------------company4---------------------OPPOSITION 2: opponentL
.
.
.
.
According to the point n.2, i would add to the previous table resported as example a column with the outcome, if it is possible to retrive the information.
How can I implement the following query that I have already built?
Code: Select all
SELECT DISTINCT a.appln_nr, 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, p.psn_name, p.psn_sector, op.oppt_name, op.oppt_nr, op.date_opp_filed
FROM tls201_appln a
full outer join tls207_pers_appln pa on pa.appln_id = a.appln_id
full outer join tls206_person p on p.person_id = pa.person_id
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
full outer join tls231_inpadoc_legal_event le on a.appln_id = le.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%'
AND op.oppt_name not like 'NULL%'
AND pa.applt_seq_nr > 0
GROUP BY a.appln_nr, 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, p.psn_name, p.psn_sector, op.oppt_name, op.oppt_nr, op.date_opp_filed
order by appln_filing_date desc, appln_id
Thank you so much for your help, I hope that my question is stated in a clear way.
Best regards,
Elisa