Beginner needs help

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: Sat Apr 26, 2014 4:35 pm

Beginner needs help

Post by King_Kong » Sat Apr 26, 2014 5:04 pm

Dear all,
I know that this is a really basic question and I hope this is the right forum for it.
I'm sorry, but this is the first time I'm working with SQL:

For my BSc final paper I'm doing a patent analysis with PATSTAT Online 2013/10 and I would like to have the patents in some specific CPC classifications, filled from October 2003 to October 2013. I did the following Query (I know that one could shorten this somehow, but I like it structured this way):

select tls206_person.doc_std_name , tls201_appln.appln_id, tls209_appln_ipc.techn_field, tls224_appln_cpc.cpc_class_symbol, tls201_appln.appln_filing_year_month, tls201_appln.appln_filing_year
from tls201_appln

join tls207_pers_appln on tls207_pers_appln.appln_id = tls201_appln.appln_id
join tls206_person on tls206_person.person_id = tls207_pers_appln.person_id
join tls224_appln_cpc on tls224_appln_cpc.appln_id = tls201_appln.appln_id
join tls209_appln_ipc on tls209_appln_ipc.appln_id = tls201_appln.appln_id

where tls201_appln.appln_filing_date = tls201_appln.prior_earliest_date
and tls201_appln.appln_filing_year < '9999'
and tls201_appln.appln_filing_year_month > '200309'
and tls224_appln_cpc.cpc_class_symbol like 'D01G%'
or tls224_appln_cpc.cpc_class_symbol like 'D01H%'
or tls224_appln_cpc.cpc_class_symbol like 'B65H%'

I got 8'268'837 results. But in these results there are patent ID's filled bevor the year 2003.
What am I doing wrong?
Doesn't this "tls201_appln.appln_filing_year_month > '200309' " mean I only want patents with a filling date after Septemeber 03?

I'd be really grateful for any advice, Thanks Simon

Posts: 140
Joined: Wed Jul 08, 2009 5:51 pm

Re: Beginner needs help

Post by nico.rasters » Wed Aug 06, 2014 8:27 pm

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 tls224_appln_cpc.cpc_class_symbol like 'B65H%')

So basically without the brackets the last two lines are the ones that found all those patents outside of your time period.
Nico Doranov
Data Manager

Daigu Academic Services & Data Stewardship

Post Reply