I would like to run a query on PATSTAT that combines CPC classes with certain keywords that could be found in the respective abstract. As a result, I would like to retrieve a table with the columns family_id, earliest_filing_year (everything from 2000 on), person_country_code, CPC-classes (grouped/aggregated into one cell).
The CPC-classes I am looking for are:
- G05D 1/021/% or
- G05D 1/02 or
- G01S 17/936 or
- G01S 17/93 or
- Y02T 90/%
I tried this query, but it does not seem to be working:
Code: Select all
SELECT DISTINCT tls201_appln.docdb_family_id, earliest_filing_year, tls206_person.person_ctry_code, CPC.CPCgrouped
FROM tls201_appln
JOIN TLS203_APPLN_ABSTR ON tls201_appln.appln_id = TLS203_APPLN_ABSTR.appln_id
JOIN tls207_pers_appln ON tls201_appln.appln_id = tls207_pers_appln.appln_id
JOIN tls206_person ON tls207_pers_appln.person_id= tls206_person.person_id
JOIN (select docdb_family_id , STRING_AGG(CAST(cpc_class_symbol AS VARCHAR(MAX)), ',')CPCgrouped from tls225_docdb_fam_cpc group by docdb_family_id ) CPC ON tls201_appln.docdb_family_id = CPC.docdb_family_id
WHERE tls201_appln.earliest_filing_year >= 2000
AND tls201_appln.earliest_filing_year < 9999
AND cpc.docdb_family_id in (select distinct docdb_family_id from tls225_docdb_fam_cpc where cpc_class_symbol = 'G05D 1/021'
OR cpc_class_symbol = 'G05D 1/02'
OR cpc_class_symbol = 'G01S 17/936'
OR cpc_class_symbol = 'G01S 17/93'
OR cpc_class_symbol LIKE 'Y02T 90/%')
AND (applt_seq_nr > 0)
AND CONTAINS (appln_abstract, 'autonomous')
OR CONTAINS (appln_abstract, 'unmanned')
AND CONTAINS (appln_abstract, 'ground')
OR CONTAINS (appln_abstract, 'street')
AND CONTAINS NOT (appln_abstract, 'air')
OR CONTAINS NOT (appln_abstract, 'aer')
OR CONTAINS NOT (appln_abstract, 'drone')
OR CONTAINS NOT (appln_abstract, 'flight')
OR CONTAINS NOT (appln_abstract, 'flies')
OR CONTAINS NOT(appln_abstract, 'fly')
group by tls201_appln.docdb_family_id, earliest_filing_year, tls206_person.person_ctry_code, CPC.CPCgrouped
order by tls201_appln.docdb_family_id, tls206_person.person_ctry_code
Best,
L.