EPO - Discussion forums

Code: Select all

SELECT DISTINCT a.appln_id,a.appln_nr, a.appln_auth, a.appln_kind, a.earliest_filing_year, t.appln_title, STRING_AGG (cast (cpc_class_symbol as varchar(max)) , '; ') CPC, a.nb_applicants, a.nb_inventors, pa.invt_seq_nr, pa.applt_seq_nr,p.psn_name, p.psn_sector, p.person_ctry_code, a.appln_filing_year, a.earliest_publn_year, a.granted, a.ipr_type
FROM tls201_appln a
join tls207_pers_appln pa on a.appln_id = pa.appln_id
join tls206_person p on pa.person_id = p.person_id
left join tls202_appln_title t on a.appln_id = t.appln_id
join tls224_appln_cpc cpc on a.appln_id = cpc.appln_id
where (a.appln_id in (select appln_id from tls224_appln_cpc where left(cpc_class_symbol,8) = 'Y02T  10'))
and a.earliest_filing_year BETWEEN 1981 and 1990
group by a.appln_id,a.appln_nr, a.appln_auth, a.appln_kind, a.earliest_filing_year, t.appln_title,a.nb_applicants, a.nb_inventors, pa.invt_seq_nr, pa.applt_seq_nr, p.person_name,p.psn_name, p.psn_sector, p.person_ctry_code, a.appln_filing_year, a.earliest_publn_year, a.granted, a.ipr_type
order by a.earliest_filing_year, a.appln_id, a.nb_applicants;

Code: Select all

SELECT DISTINCT a.docdb_family_id, a.docdb_family_size,a.appln_nr, a.appln_auth, a.appln_kind, a.earliest_filing_year, t.appln_title, STRING_AGG (cast (cpc_class_symbol as varchar(max)) , '; ') CPC, a.nb_applicants, a.nb_inventors, pa.invt_seq_nr, pa.applt_seq_nr,p.psn_name, p.psn_sector, p.person_ctry_code, a.appln_filing_year, a.earliest_publn_year, a.granted, a.ipr_type
FROM tls201_appln a
join tls207_pers_appln pa on a.appln_id = pa.appln_id
join tls206_person p on pa.person_id = p.person_id
left join tls202_appln_title t on a.appln_id = t.appln_id
join tls224_appln_cpc cpc on a.appln_id = cpc.appln_id
where (a.appln_id in (select appln_id from tls224_appln_cpc where left(cpc_class_symbol,8) = 'Y02T 10'))
and a.earliest_filing_year BETWEEN 1981 and 1990
group by a.docdb_family_id, a.docdb_family_size, a.appln_id ,a.appln_nr, a.appln_auth, a.appln_kind, a.earliest_filing_year, t.appln_title,a.nb_applicants, a.nb_inventors, pa.invt_seq_nr, pa.applt_seq_nr, p.person_name,p.psn_name, p.psn_sector, p.person_ctry_code, a.appln_filing_year, a.earliest_publn_year, a.granted, a.ipr_type
order by a.earliest_filing_year, a.docdb_family_id, a.nb_applicants;