For most patent authorities this works fine but for some countries the internat_appln_id is not available or data is incomplete.
An example is Columbia, PATSTAT (2018b) contains 12.691 publications with the kind code "A2" (Application derived from a PCT application), however 11.364 do not have a link via the internat_appln_id to a PCT application. Most of those applications do however have a PCT member in the docdb family which reasonably spoken can be considered as the PCT filing from which the CO applications originate.
Code: Select all
select tls211_pat_publn.publn_auth+publn_nr+publn_kind as publication, publn_date ,tls201_appln.internat_appln_id,
(case when tls201_appln.internat_appln_id = 0 then pct.appln_nr_epodoc end) PCT
from tls211_pat_publn join tls201_appln on tls211_pat_publn.appln_id = tls201_appln.appln_id
left join tls201_appln family on tls201_appln.docdb_family_id = family.docdb_family_id
left join tls201_appln pct on family.internat_appln_id = pct.appln_id and pct.appln_id < 900000000
where publn_auth = 'CO' and publn_kind = 'A2'
order by publn_date,tls211_pat_publn.appln_id