Not sure if your question is still relevant (and you are posting on the wrong board btw) and I can not test this because I do not have access to PATSTAT Online, but... you missed some brackets: and (tls224_appln_cpc.cpc_class_symbol like 'D01G%' or tls224_appln_cpc.cpc_class_symbol like 'D01H%' or t...

You are probably posting in the wrong forum, unless the Global Patent Index is something that was added to the latest PATSTAT edition? if you have PATSTAT you can use `tls212_citation`.`npl_publn_id` to get a count of NPL references. Don't use MAX(`npl_citn_seq_nr`). That only gives the max number f...

Do you have access to a local installation of PATSTAT (if so, which version?) or PATSTAT Online? The following query would give you a list of names that have "TEVA" in them. SELECT * FROM `tls206_person` WHERE `person_name` REGEXP "[[:<:]]TEVA[[:>:]]"; Alternatively, you can use ...

Sorry this post has remained unanswered for such a long time. From the names of the tables in your post, I assume that you are trying to use the OECD Citations database, the OECD REGPAT database, and PATSTAT. You mention PATSTAT April 2013. Is that the version you are using? I'm on October 2013, so ...

Good question. Maybe someone with access to TLS221_INPADOC_PRS can answer this? http://www.epo.org/searching/subscription/raw/product-14-24-1.html Theoretically I would say that all kind of applications can be present in PATSTAT. It is snapshot data after all. There are around 50.000 patents in the ...

I ran the following query on PATSTAT October 2013. SELECT * FROM `tls206_person` INNER JOIN `tls207_pers_appln` ON `tls206_person`.`person_id` = `tls207_pers_appln`.`person_id` INNER JOIN `tls218_docdb_fam` ON `tls207_pers_appln`.`appln_id` = `tls218_docdb_fam`.`appln_id` WHERE `tls218_docdb_fam`.`d...

First you should reduce the problem. Only patents with multiple applicants are an issue. For patents with multiple applicants there is no direct link between applicant and inventor, but as you are using REGPAT you can use the Ctry_code and Reg_code fields. For example, (based on REGPAT Jan 2014) for...

It's best not to double post. Not a very easy question btw. I assume you are using PATSTAT Online? Instead of the long WHERE (hrm_l2_id= OR OR OR) you could do WHERE `hrm_l2_id` IN (1,2,3,4,etc.) And those IDs are numbers, so no need to put them within quotes. Also you should do a LEFT OUTER JOIN wi...

You could use http://gder.phpnet.org/The_worldwide_count_of_priority_patents.html in combination with the EEE-PPAT database. Alternatively there's TLS206_PERSON (dirty names), TLS208_DOC_STD_NMS (standardized by the EPO... they themselves say it's low quality) and the OECD HAN database. For your und...

http://www.esf-ape-inv.eu/ is supposed to deal with that. And at http://gder.phpnet.org/The_worldwide_count_of_priority_patents.html you can see some code to find an inventor's country if it's missing. Having more information is always helpful to distinguish between inventors with the same name. I w...