Counting patents by IPC subclass
Posted: Mon Nov 23, 2020 7:05 pm
Dear PatStat experts:
I'm counting patents by IPC subclass.
Something like the following query worked fine a year ago (with PatStat Online 2019 Autumn), but now I'm getting an error (with the 2020 Autumn): Invalid column name 'ipc_subclass'.
Did some SQL/PatStat syntax change between November 2019 and now (e.g., about the use of 'AS'), or am I missing something else?
I tried to spot such a change between the 2019 & the 2020 versions of the PATSTAT manuals & data catalogues, but couldn't find one.
I'm counting patents by IPC subclass.
Something like the following query worked fine a year ago (with PatStat Online 2019 Autumn), but now I'm getting an error (with the 2020 Autumn): Invalid column name 'ipc_subclass'.
Code: Select all
SELECT psn_id, psn_name, a.appln_filing_year, LEFT(ipc_class_symbol,4) AS ipc_subclass, COUNT(*) AS patcount -- count patents by (firm, year, IPC subclass)
FROM tls206_person p
JOIN tls207_pers_appln pa ON p.person_id = pa.person_id
JOIN tls201_appln a ON pa.appln_id = a.appln_id
LEFT OUTER JOIN tls209_appln_ipc i ON a.appln_id = i.appln_id
WHERE a.appln_auth IN ('EP','US') -- limit to "cooperative" offices
AND a.appln_filing_year BETWEEN 1966 AND 2015
AND a.appln_id = a.earliest_filing_id -- limit to first filings
AND pa.applt_seq_nr > 0 -- applicants only
AND psn_name <> '-NOT AVAILABLE-' -- drop if person's name is missing
AND psn_id IN
(13138464)
GROUP BY psn_id, psn_name, a.appln_filing_year, ipc_subclass
ORDER BY psn_id, psn_name, a.appln_filing_year, ipc_subclass
I tried to spot such a change between the 2019 & the 2020 versions of the PATSTAT manuals & data catalogues, but couldn't find one.