How to get multiple applicants without exceeding patstat limits

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

Posts: 1
Joined: Mon Nov 09, 2020 5:23 pm

How to get multiple applicants without exceeding patstat limits

Post by ValB » Mon Nov 09, 2020 5:31 pm

Dear reader,

For my research I need to create a (interfirm) patent collaboration network. I don't want to include inventors, but education or university collaborations with eachother and/or other firms are fine. Also I would like at least one of the applicants (/firms) to be based in the Netherlands.
I am wondering how to query this in sql without creating a table/dataset per amount of applicants and without exceeding Patstat Online's limitations.

Subsequently I am also curious how to eliminate or prevent the duplicate rows that result from my current method (where the p1 of the row above becomes p2).

My current code is this;

Code: Select all

select a.appln_id, tit.appln_title,
p1.doc_std_name as name1, p1.person_ctry_code as cc1, p1.person_address as addres1, free1.address_freeform,
p2.doc_std_name as name2, p2.person_ctry_code as cc2, p2.person_address as addres2, free2.address_freeform,
p3.doc_std_name as name3, p3.person_ctry_code as cc3, p3.person_address as addres3, free3.address_freeform,
p4.doc_std_name as name4, p4.person_ctry_code as cc4, p4.person_address as addres4, free4.address_freeform,
a.granted, a.ipr_type, cpc.cpc_class_symbol as cpc_class, ab.appln_abstract
from tls206_person p1
join tls207_pers_appln pa1 on p1.person_id = pa1.person_id
join tls207_pers_appln pa2 on pa1.appln_id = pa2.appln_id
join tls207_pers_appln pa3 on pa1.appln_id = pa3.appln_id
join tls207_pers_appln pa4 on pa1.appln_id = pa4.appln_id
join tls201_appln a on pa1.appln_id = a.appln_id
join tls202_appln_title tit on pa1.appln_id = tit.appln_id
join tls203_appln_abstr ab on pa1.appln_id = ab.appln_id
join tls224_appln_cpc cpc on pa1.appln_id = cpc.appln_id
join tls206_person p2 on pa2.person_id = p2.person_id
join tls206_person p3 on pa3.person_id = p3.person_id
join tls206_person p4 on pa4.person_id = p4.person_id
join tls226_person_orig free1 on p1.person_id = free1.person_id
join tls226_person_orig free2 on p2.person_id = free2.person_id
join tls226_person_orig free3 on p3.person_id = free3.person_id
join tls226_person_orig free4 on p4.person_id = free4.person_id
where (p1.person_ctry_code = 'NL' ) 
	and (pa1.applt_seq_nr > 0) 
    and(pa2.applt_seq_nr > 0)
    and(pa3.applt_seq_nr > 0)
    and(pa4.applt_seq_nr > 0)
    and (pa1.invt_seq_nr <1)
    and(pa2.invt_seq_nr < 1)
    and(pa3.invt_seq_nr < 1)
    and(pa4.invt_seq_nr < 1)
    and(pa1.person_id != pa2.person_id)
    and(pa1.person_id != pa3.person_id)
    and(pa2.person_id != pa3.person_id)
    and(pa4.person_id != pa1.person_id)
    and(pa4.person_id != pa2.person_id)
    and(pa4.person_id != pa3.person_id)
    and (a.earliest_filing_year between 2008 and 2013) 
    and (a.nb_applicants=4)
group by p1.doc_std_name, p1.person_ctry_code,p1.person_address, p4.doc_std_name, p4.person_ctry_code, p4.person_address, free4.address_freeform, p2.doc_std_name, p2.person_ctry_code,p2.person_address, a.appln_id, a.granted, a.ipr_type, tit.appln_title, ab.appln_abstract, cpc.cpc_class_symbol, free1.address_freeform, free2.address_freeform, p3.doc_std_name, p3.person_ctry_code, p3.person_address, free3.address_freeform
I would be very grateful for your advice.

Kind regards,


Post Reply