I have a set of specific patent, for example, appln_id= 16429394, I expect to set up another set of samples, which have the same filing year and ipc 4-digit classification with this patent. The code works well at my local computer, but can't work on patstat online database. Is there anything I can do? many thanks.
code:
select* from tls201_appln t4 join tls209_appln_ipc t5 on t4.appln_id=t5.appln_id
join (select appln_filing_year, ipc_subclass_symbol from tls201_appln t1
join tls209_appln_ipc t2 on t1.appln_id=t2.appln_id where t1.appln_id=16429394) t3
on t4.appln_filing_year=t3.appln_filing_year and t5.ipc_subclass_symbol=t3.ipc_subclass_symbol
question about join on multiple column
Re: question about join on multiple column
Hi liuxia0510,
Your query is correct and should run on PATSTAT Online. We will analyse and correct this issue.
As a work around, I reformulated your query. IMO it has a simpler structure now, returns the same result and runs on PATSTAT Raw Data as well as on PATSTAT Online. I also added the DISTINCT clause to avoid duplicates in the result.
Best regards,
Martin
Your query is correct and should run on PATSTAT Online. We will analyse and correct this issue.
As a work around, I reformulated your query. IMO it has a simpler structure now, returns the same result and runs on PATSTAT Raw Data as well as on PATSTAT Online. I also added the DISTINCT clause to avoid duplicates in the result.
Code: Select all
-- retrieve all applications and their IPCs which have the same filing year and one of the IPCs of appln_ID 16429394
-- tables a1 and i1 are used for output
-- tables a2 and i2 are used for restricting the output
-- returns 30 479 rows on 2015 Spring Edition
select distinct a1.*, i1.*
from tls201_appln a1
join tls209_appln_ipc i1 on a1.appln_id=i1.appln_id
join tls201_appln a2 on year(a1.appln_filing_date) = year(a2.appln_filing_date)
join tls209_appln_ipc i2 on a2.appln_id=i2.appln_id
and left(i1.ipc_class_symbol, 4) = left(i2.ipc_class_symbol, 4)
where a2.appln_id=16429394
Martin
-------------------------------------------
Martin Kracker / EPO
Martin Kracker / EPO