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.
-
LorensET
- Posts: 3
- Joined: Fri Mar 24, 2023 9:56 am
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:
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