Counting patents by IPC subclass

Here you can post your opinions, ask questions and share experiences on the PATSTAT product line. Please always indicate the PATSTAT edition (e.g. 2015 Autumn Edition) and the database (e.g. PATSTAT Online, MySQL, MS SQL Server, ...) you are using.
Post Reply

Michi
Posts: 2
Joined: Mon Nov 23, 2020 6:52 pm

Counting patents by IPC subclass

Post by Michi » 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'.

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
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.


mkracker
Posts: 120
Joined: Wed Sep 04, 2013 6:17 am
Location: Vienna

Re: Counting patents by IPC subclass

Post by mkracker » Tue Nov 24, 2020 7:43 am

Dear PATSTAT user,

Thanks you for your precise question. However, I doubt that your query ever worked.

The error "Invalid column name 'ipc_subclass' " is in the GROUP BY clause. The attribute "ipc-subclass" does not exist in the database. It is just defined in the SELECT clause as an alias for "LEFT(ipc_class_symbol,4)". So it must not be used in the query, except in the ORDER BY clause, where you may use any of the attributes, functions or aliases of the SELECT clause.

But the fix is simple. Just change your ORDER BY clause to this:
GROUP BY psn_id, psn_name, a.appln_filing_year, LEFT(ipc_class_symbol,4)
-------------------------------------------
Martin Kracker / EPO


Michi
Posts: 2
Joined: Mon Nov 23, 2020 6:52 pm

Re: Counting patents by IPC subclass

Post by Michi » Wed Nov 25, 2020 12:41 am

Dear Martin,

Now I see what's going on. Thank you very much for clarifying the underlying problem and the solution!

Best,
Michi


Post Reply