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
Include non-patent citations in query
-
- Posts: 440
- Joined: Thu Feb 22, 2007 5:33 pm
- Contact:
Re: Include non-patent citations in query
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:
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'
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
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
EPO - Vienna
patstat @ epo.org
Re: Include non-patent citations in query
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
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