X-categorized citations

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

Vaal05
Posts: 3
Joined: Tue Oct 27, 2020 2:54 pm

X-categorized citations

Post by Vaal05 » Tue Oct 27, 2020 4:12 pm

Dear all,

I am working on Patstat Online (Spring 2020 version) and trying to get the count of X-categorized citations in applications of a set of patents. The following code is an attempt to do so for one specific patent with appln_id = 56156. If I understand well, I should find 6 X-categorized citations, same as in the search report of the A1 publication - that can be found here:https://worldwide.espacenet.com/patent/ ... 2143942%22. But the output of my query is quite different: some citations that are not from the search report are categorized while some other from the search report are not.

Code: Select all

SELECT t1.appln_id, t5.publn_nr, t10.publn_nr as cited_publn_nr, t10.publn_auth, t12.citn_categ, t5.publn_claims
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 tls201_appln t11 ON t10.appln_id = t11.appln_id
LEFT JOIN tls215_citn_categ t12 ON t6.cited_pat_publn_id = t12.pat_publn_id
WHERE t1.appln_id = 56156
AND t6.cited_pat_publn_id > 0
AND t5.publn_kind = 'A1'
GROUP BY t1.appln_id, t5.publn_nr, t10.publn_nr, t10.publn_auth, t12.citn_categ, t5.publn_claims
What am I doing wrong?

Thanks for helping,
Val


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

Re: X-categorized citations

Post by EPO / PATSTAT Support » Wed Oct 28, 2020 11:41 am

Hello Val,
there is a small logical mistake in your query.
The tls215_citn_categ should be joined via 2 attributes in order to link the correct citation category to the citations: t6.pat_publn_id = t12.pat_publn_id and t6.citn_id = t12.citn_id
So observe that the join is NOT via t6.cited_pat_publn_id.
This is a bit of a pitfall because it is contra-intuitive.

Code: Select all

SELECT t1.appln_id, t5.publn_auth+t5.publn_nr, t5.publn_kind,t6.pat_citn_seq_nr,
t10.publn_auth+t10.publn_nr as cited_publn_nr, t12.citn_categ, citn_origin,t5.publn_claims
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
WHERE t1.appln_id = 56156
AND t6.cited_pat_publn_id > 0
AND t5.publn_kind = 'A1'
GROUP BY t1.appln_id, t5.publn_auth+t5.publn_nr, t10.publn_nr, t5.publn_kind,
t10.publn_auth+t10.publn_nr, t12.citn_categ,citn_origin,t5.publn_claims, t6.pat_citn_seq_nr
order by citn_origin,t6.pat_citn_seq_nr
PATSTAT Support Team
EPO - Vienna
patstat @ epo.org


Vaal05
Posts: 3
Joined: Tue Oct 27, 2020 2:54 pm

Re: X-categorized citations

Post by Vaal05 » Thu Oct 29, 2020 10:08 am

Thank you for your help!

Best,
Val


Post Reply