selecting all patents from a firm belonging to a subset

[lorenking]

Hi there,

I'm interested in understanding firms' innovative history both within a particular IPC class and in general.
I will need to do this for multiple technologies but, as a first attempt, I started with nuclear power.

Firstly, I ran a query identifying all granted priority patents in nuclear post 1960. the query looks like this:

Code: Select all

select distinct tls201_appln.appln_id , tls201_appln.earliest_filing_year , tls201_appln.inpadoc_family_id , tls201_appln.docdb_family_id , tls201_appln.nb_applicants , tls201_appln.nb_inventors , tls201_appln.appln_auth , tls206_person.hrm_l2_id , tls206_person.hrm_l2 , tls206_person.person_ctry_code from tls201_appln , tls209_appln_ipc , tls206_person , tls207_pers_appln
where tls201_appln.appln_id = tls209_appln_ipc.appln_id
and tls201_appln.appln_filing_year >= '1960'
and (tls209_appln_ipc.ipc_class_symbol like 'G21%'  or tls209_appln_ipc.ipc_class_symbol like 'F02C___1/05%')
and tls201_appln.appln_id= tls201_appln.earliest_filing_id
and tls201_appln.granted=1
and tls201_appln.appln_id = tls207_pers_appln.appln_id
and tls206_person.person_id= tls207_pers_appln.person_id

That worked great and returns 174,000 rows (though only 47000 patents)

From this table, I get various firm IDs (using hrm_I2_id) and now I'd like to identify all the patents (regardless of IPC category) of these firms. Here is what I write:

Code: Select all

select distinct tls201_appln.appln_id , tls201_appln.earliest_filing_year , tls206_person.hrm_l2_id from tls201_appln , tls206_person 
where 
(select tls206_person.hrm_l2_id from tls201_appln , tls209_appln_ipc , tls206_person , tls207_pers_appln
where tls201_appln.appln_id = tls209_appln_ipc.appln_id
and tls201_appln.appln_filing_year >= '1960'
and (tls209_appln_ipc.ipc_class_symbol like 'G21%'  or tls209_appln_ipc.ipc_class_symbol like 'F02C___1/05%')
and tls201_appln.appln_id= tls201_appln.earliest_filing_id
and tls201_appln.granted=1
and tls201_appln.appln_id = tls207_pers_appln.appln_id
and tls206_person.person_id= tls207_pers_appln.person_id)
and tls201_appln.appln_id= tls201_appln.earliest_filing_id

It looks long.. but all I'm trying to do is identify all priority patents (and year of filling) for this subset of firms.

However I get the following error: 4:21:48 PM [SELECT - 0 row(s), 0 secs] [Error Code: 103, SQL State: S0004] The identifier that starts with 'select tls206_person.hrm_l2_id from tls201_appln , tls209_appln_ipc , tls206_person , tls207_pers_appln where tls201_appln.appln' is too long. Maximum length is 128.

Is it possible that the query is not possible online? Or is there an easier way to search?

If it helps reduce the amount of results, a table that returns all patents for those firms who have at least 2/3 nuclear patents would also be fine too... though ideally, I could get it for all of them!

Hope this is clear.

Thank you!

Loren

[EPO / EPAL Team]

Dear Loren,

Few of the attributes you are using have changed with the Spring Edition. tls206_person.hrm_l2_id is now tls206_person.psn_id. Please have a look at the latest PATSTAT data catalog where you will find all the détails: http://documents.epo.org/projects/babyl ... _5_07.pdf
sample query for PATSTAT Spring edition:

select t6.psn_name, count(a2.appln_id) As NbOfAppl
from (select i.appln_id
from tls201_appln a
join tls209_appln_ipc i on i.appln_id = a.appln_id
where a.appln_filing_year >= 1960
and (i.ipc_class_symbol like 'G21%' or i.ipc_class_symbol like 'F02C___1/05%')
and a.appln_id= a.earliest_filing_id
and a.granted=1) t1
join tls201_appln a2 on t1.appln_id=a2.appln_id
join tls207_pers_appln t7 on t7.appln_id = a2.appln_id
join tls206_person t6 on t6.person_id = t7.person_id
where a2.appln_id= a2.earliest_filing_id
group by t6.psn_name
order by NbOfAppl desc

As an alternative you could also use GPI to retrieve the top 50 applicants based on a classification search. The simple statistics makes it then possible to copy all the applicants into a new query.

best regards,

[lorenking]

Dear Epal,

Thank you! I was using the Autumn 2015 version (and should have said so) as I'm more familiar with it.. But I should probably use 2016 since it is more up to date!
I ran your query and it seems to work great. I wanted the year of filing for each application so I tweaked it slighlty.. But as far as I understand, I now have a table with all patent applications from those individuals/firms who have at least one patent with ipc symbol G21%, correct?

Again thank you very much! I kept getting a error message saying my query exceeded the maximum cost allowed on patstat online... Somehow the way you wrote it didn't seem to cause any issue!

Cheers,

Loren