Searching with low-carbon classification

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

LorensET
Posts: 3
Joined: Fri Mar 24, 2023 9:56 am

Searching with low-carbon classification

Post by LorensET » Wed Mar 29, 2023 10:19 am

Hi,
I'm interested in a SQL code that retrieves all low-carbon patens filed at EPO. I'm rather new to SQL's, but I've tried the following:

Code: Select all

SELECT DISTINCT docdb_family_id, appln_filing_year, appln_title 
FROM tls201_appln 
INNER JOIN tls211_pat_publn 
ON tls201_appln.appln_id = tls211_pat_publn.appln_id 
INNER JOIN tls209_appln_ipc 
ON tls201_appln.appln_id = tls209_appln.appln_id 
WHERE tls209_appln.ipc_class_symbol LIKE 'Y02%' 
AND tls201_appln.appln_auth = 'EP'
Here i get the following error: [Error Code: 4104, SQL State: S0001] The multi-part identifier "tls209_appln.appln_id" could not be bound.

What am i doing wrong here? I would appreciate any help!


Kind regards,
Lorens


EPO / PATSTAT Support
Posts: 425
Joined: Thu Feb 22, 2007 5:33 pm
Contact:

Re: Searching with low-carbon classification

Post by EPO / PATSTAT Support » Thu Mar 30, 2023 1:21 pm

There is a typo in your SQL, and you have the appln_title in the SELECT part without having the tls202_appln_title in your joined tables. Also the Y-tags are part of the CPC scheme (table tls225_docdb_fam_cpc), and are not availalble in the IPC table. You have also joined the tls212 table, but you don't use any of the attributes in your SELECT (or WHERE) clause. So it can be removed.
This query will create a large amount of rows. Here is a reworked (and more limited) SQL example:

Code: Select all

SELECT DISTINCT tls201_appln.docdb_family_id,appln_auth+appln_nr application ,appln_filing_date, appln_title 
FROM tls201_appln JOIN tls225_docdb_fam_cpc ON tls201_appln.appln_id = tls225_docdb_fam_cpc.docdb_family_id
left JOIN tls202_appln_title on tls201_appln.appln_id = tls202_appln_title.appln_id
WHERE tls225_docdb_fam_cpc.cpc_class_symbol LIKE 'Y02%' 
AND tls201_appln.appln_auth = 'EP'
and appln_filing_year > 2000
order by appln_filing_date asc
PATSTAT Support Team
EPO - Vienna
patstat @ epo.org


Post Reply