Include non-patent citations in query

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

alkis.pit
Posts: 2
Joined: Thu May 26, 2022 5:39 pm

Include non-patent citations in query

Post by alkis.pit » Thu May 26, 2022 5:50 pm

Hello,

I'm in need of your help. I need to find the patents under specific companies (let's say nokia solutions) but also include any NPL references, specifically include from TLS214_NPL_PUBL, the NPL_TYPE = a (i.e. Abstract citation of no specific kind)

my code is
select *from tls201_appln a
join tls207_pers_appln pa on a.appln_id = pa.appln_id
join tls206_person p on pa.person_id = p.person_id
where person_name like 'nokia solutions %'

Any help to incorporate the above into the code would be gratly appreaciated.

Thank you


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

Re: Include non-patent citations in query

Post by EPO / PATSTAT Support » Mon May 30, 2022 9:13 am

Hello alkis.pit,

When looking for patents linked to a certain applicant it is better to use the PATSTAT standardised name. (psn_name). The best way to find out which psn_name(s) is the best for your purpose is to run a quick query doing a patent count.
Something like this:

Code: Select all

SELECT psn_name, Count(tls207_pers_appln.appln_id) total
FROM tls206_person join tls207_pers_appln 
on tls206_person.person_id = tls207_pers_appln.person_id
where psn_name like 'nokia%' and applt_seq_nr > 0
group by psn_name
order by total desc
Which shows that "Nokia Solutions" is probably not what you want when looking at the number of patent filings under that name.

And here is a sample query that gives you all the cited NPL for all applications filed by the condition psn_name = 'NOKIA NETWORKS'

Code: Select all

SELECT  psn_name,citing.appln_id, citing.publn_auth, citing.publn_nr, 
citing.publn_kind, citing.publn_date, tls212_citation.*,
cited.publn_auth, npl_biblio,npl_author, npl_title1, npl_publn_date, 
online_availability
  FROM tls211_pat_publn citing join tls227_pers_publn  on citing.pat_publn_id = tls227_pers_publn.pat_publn_id
  join tls206_person on tls227_pers_publn.person_id = tls206_person.person_id  
  join tls212_citation on citing.pat_publn_id = tls212_citation.pat_publn_id
  join tls211_pat_publn cited on tls212_citation.cited_pat_publn_id = cited.pat_publn_id
  join tls214_npl_publn on tls212_citation.cited_npl_publn_id = tls214_npl_publn.npl_publn_id 
  where 
  psn_name = 'NOKIA NETWORKS' and applt_seq_nr> 0
  and tls212_citation.cited_npl_publn_id <> '0'
order by citing.publn_date desc, citing.appln_id, npl_citn_seq_nr asc
PATSTAT Support Team
EPO - Vienna
patstat @ epo.org


alkis.pit
Posts: 2
Joined: Thu May 26, 2022 5:39 pm

Re: Include non-patent citations in query

Post by alkis.pit » Tue May 31, 2022 9:30 am

Thank you so much!

However, I also need most of the information the original code gives, like appln_kind, ipr_type, internat_appln_id, int_phase, reg_phase, earliest_filing_year, granted, nuts, psn_sector.

could you please advise.

Thank you again for your help


Post Reply