generate list of EP applications having at least one X-citation

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

Chrish
Posts: 1
Joined: Thu May 25, 2023 9:30 pm

generate list of EP applications having at least one X-citation

Post by Chrish » Sat Jun 03, 2023 4:09 pm

Dear all,

I wish to generate a list of EP applications of a certain CPC class which contain a European search report. The European Search reports of these EP applications shall contain at least one X-citation.

I have found some code snippets in the forum and your sample queries / PATSTAT manual, but I am struggling to put is correctly together. Could you please help me with this?

Christoph

Code: Select all

FROM tls201_appln t1
JOIN tls211_pat_publn t5 ON t1.appln_id = t5.appln_id
JOIN tls212_citation t6 ON t5.pat_publn_id = t6.pat_publn_id
JOIN tls211_pat_publn t10 ON t6.cited_pat_publn_id = t10.pat_publn_id
LEFT JOIN tls215_citn_categ t12 ON t6.pat_publn_id = t12.pat_publn_id and t6.citn_id = t12.citn_id -- not sure whether all these JOINs are necessary or some are missing??
WHERE
cpc_class_symbol= 'A61' 
and citn_origin in ('SEA') -- limits the citations to ones in the EP search.
and search-type=("nat", "sup") -- only searchreports of the EPO, i.e. no ISR - maybe not necessary due to the code line above??
and citn_categ in ('X') -- limits the citations strictly to X citations (not A for example, as specified by you).
ORDER BY tls201_appln.appln_id


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

Re: generate list of EP applications having at least one X-citation

Post by EPO / PATSTAT Support » Wed Jun 07, 2023 11:42 am

A bit reworked, have a look and check.
The list does not include cited NPL !

Code: Select all

select distinct t1.appln_id,t1.appln_auth+t1.appln_nr+t1.appln_kind application, t1.appln_filing_date filing_date,
t5.publn_auth+t5.publn_nr+t5.publn_kind citing_publication, t5.publn_date citing_publication_date,
t10.publn_auth+t10.publn_nr+t10.publn_kind cited_publication,t10.publn_date cited_publication_date,
t6.citn_origin, t12.citn_categ, t6.pat_citn_seq_nr
FROM tls201_appln t1
JOIN tls211_pat_publn t5 ON t1.appln_id = t5.appln_id
JOIN tls212_citation t6 ON t5.pat_publn_id = t6.pat_publn_id
JOIN tls211_pat_publn t10 ON t6.cited_pat_publn_id = t10.pat_publn_id
JOIN tls215_citn_categ t12 ON t12.pat_publn_id = t6.pat_publn_id and  t12.citn_id = t6.citn_id
WHERE
t1.appln_id in (select appln_id from tls224_appln_cpc where left(cpc_class_symbol,3) = 'A61' )
and citn_origin in ('SEA') 
and citn_gener_auth = 'EP' -- limits the citations to ones in the EP search.
and t5.publn_kind in ('A1', 'A3','A4') -- only searchreports of the EPO, i.e. no ISR - maybe not necessary due to the code line above??
and citn_categ in ('X') -- limits the citations strictly to X citations (not A for example, as specified by you).
and year(t5.publn_date) = 2022
and t6.cited_pat_publn_id <> 0
ORDER BY t1.appln_filing_date desc ,t1.appln_id, t6.pat_citn_seq_nr
PATSTAT Support Team
EPO - Vienna
patstat @ epo.org


Post Reply