Problems from nested SELECT

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

jon.charterina@ehu.eus
Posts: 4
Joined: Tue Mar 12, 2019 9:00 am

Problems from nested SELECT

Post by jon.charterina@ehu.eus » Tue Mar 12, 2019 11:59 am

Hi,
I am new in this trade. I am asking for help regarding the command I put here (below). I want to retrieve a list of patents containing the number of publications, claims, cited patents, cited articles and data from tls201_appln (main table), and tls209_appln_ipc (restriction to IPCs)
All the time the message I get is:
[SELECT - 0 row(s), 0 secs] [Error Code: 156, SQL State: S0001] Incorrect syntax near the keyword 'FROM'

I'm sure it is a silly mistake, but as I don't have other training with SQL than just a few days, it is hard to solve. Here is the full sentence:

SELECT tls201_appln.*, 'Publications', 'Claims', 'CitedPatents', 'CitedArts', tls209_appln_ipc.*
(SELECT COUNT(*) AS 'Publications', SUM(tls211_pat_publn.publn_claims) AS 'Claims',
SUM( tls212_citation.cited_pat_publn_id) AS 'CitedPatents', SUM(tls212_citation.cited_npl_publn_id) AS 'CitedArts'
FROM tls211_pat_publn, tls212_citation
JOIN tls211_pat_publn ON tls212_citation.pat_publn_id = tls211_pat_publn.pat_publn_id)
FROM tls201_appln, tls211_pat_publn, tls212_citation, tls209_appln_ipc
JOIN tls209_appln_ipc ON tls201_appln.appln_id = tls209_appln_ipc.appln_id
WHERE(( tls209_appln_ipc.ipc_class_symbol LIKE 'B82Y%'
or tls209_appln_ipc.ipc_class_symbol LIKE 'B81C%'
or tls209_appln_ipc.ipc_class_symbol LIKE 'B82B%') -- NANOTECHNOLOGY
AND tls201_appln.granted= 'Y'
AND tls201_appln.publn_first_grant='Y'
AND tls201_appln.earliest_publn_year= 2002)
GROUP BY tls201_appln.appln_id
ORDER BY tls201_appln.appln_id


Any suggestion is welcome!
Thanks!
Jon


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

Re: Problems from nested SELECT

Post by EPO / PATSTAT Support » Wed Mar 13, 2019 11:55 am

Hello Jon,
SQL queries mostly have a structure based on SELECT- FROM -WHERE - GROUP BY in that specific order.
On the PATSTAT webpage you can find a number of documents with sample queries and self-study publications which you can try out on PATSTAT Online.
I have re-worked your query to something that does run, but I am not sure if your intention is really to count cited patents and cited NPL.
I have removed the condition " AND tls201_appln.publn_first_grant='Y' ", because that would only look at the citations that are strictly connected to the publication of the granted patent. For EP applications, this would mean citations noted in the B1 document that have for example been added during the examination process, while the citations from the search report will be listed in the A1 or A3 publications. By removing that conditions, you will anyway see both (all) publications and their respective citations. I left in place he condition " tls201_appln_granted = 'Y' "; this restricts your data set to only take into account those applications that were granted.
I have also moved your IPC condition to the WHERE clause instead of joining the tables because a JOIN creates extra rows (an extra one for each IPC code) and you would have many duplicate rows where only the IPC code is different. I assume you only need this table to restrict your data set to NANOTECH applications, but that you're not further interested in knowing which one of the IPC codes exactly is linked to the application. Here is the query:

Code: Select all

SELECT tls201_appln.appln_id, tls201_appln.appln_nr_epodoc, 
tls211_pat_publn.publn_auth+tls211_pat_publn.publn_nr+tls211_pat_publn.publn_kind publication,
publn_claims,
COUNT((case when cited_pat_publn_id <> 0 then cited_pat_publn_id end)) as "cited_publications",
COUNT((case when cited_npl_publn_id <> 0 then cited_npl_publn_id end)) as "cited_npl_publn_id"
FROM tls201_appln join tls211_pat_publn on tls201_appln.appln_id = tls211_pat_publn.appln_id
	JOIN tls212_citation on tls212_citation.pat_publn_id = tls211_pat_publn.pat_publn_id
WHERE
tls201_appln.appln_id in 
	(select distinct appln_id from tls209_appln_ipc 
		where ( left(tls209_appln_ipc.ipc_class_symbol,4 ) = 'B82Y' 
		or left(tls209_appln_ipc.ipc_class_symbol,4 )LIKE 'B81C'
		or left(tls209_appln_ipc.ipc_class_symbol,4 ) LIKE 'B82B')) -- NANOTECHNOLOGY
 AND tls201_appln.granted= 'Y'
 AND tls201_appln.earliest_publn_year= 2002
Group by tls201_appln.appln_id, tls201_appln.appln_nr_epodoc, 
 tls211_pat_publn.publn_auth+tls211_pat_publn.publn_nr+tls211_pat_publn.publn_kind, publn_claims
ORDER BY tls201_appln.appln_id,
	tls211_pat_publn.publn_auth+tls211_pat_publn.publn_nr+tls211_pat_publn.publn_kind
PATSTAT Support Team
EPO - Vienna
patstat @ epo.org


jon.charterina@ehu.eus
Posts: 4
Joined: Tue Mar 12, 2019 9:00 am

Re: Problems from nested SELECT

Post by jon.charterina@ehu.eus » Wed May 22, 2019 12:07 pm

Thank you very much for your help!
I have copy-pasted your sentences.
Best,
JCh


Post Reply