Question re TLS211_PAT_PUBLN

[talya.ponchek]

*** Sorry for the duplicate posts, just realize this is the appropriate thread ***

Hello again,
I'm reading the PATSTAT data catalog v.5 06 and I have a question re row data in TLS211_PAT_PUBLN.
Do I understand correctly that the data in TLS211 when related to TLS201 provides additional information on applications which were published? Is the publication an application publication or a post grant publications.

After I posted the above here in the forum I tried the following:
from
TLS201_APPLN app,
TLS224_APPLN_CPC cpc,
TLS209_APPLN_IPC ipc,
TLS211_PAT_PUBLN publn
where 1=1
and app.appln_id=cpc.appln_id
and app.appln_id=ipc.appln_id
and app.appln_id=publn.appln_id
and app.ipr_type='PI'
--and app.granted=1
and app.appln_auth in ('DE','IL')
and cpc.CPC_CLASS_SYMBOL in (
'H01M 10/00',
'H01M2010/00',
'H01M 6/00',
'H01M 2/00',
'H01M 8/00',
'Y10S 977/948',
'H01M 12/00',
'Y02E 60/12',
'B60L2240/54',
'B60Y2400/112',
'B60Y2400/114',
'Y02T 10/7005',
'Y10S 903/908',
'Y10S 903/903',
'B60L 11/1851',
'B60L 11/1881',
'B60L 11/1879',
'Y02T 10/6273',
'Y02T 10/7072',
'B60Y2300/91',
'H02J 7/00',
'H02J 7/0027',
'G01R 31/36',
'Y10S 320/00',
'Y10S 320/21',
'Y10S 320/12',
'H02J2007/00',
'H02J 7/14',
'B60Y2400/435',
'Y02T 10/7072',
'G01R 31/3624',
'G01R 31/3606',
'B60L 11/1809',
'Y02T 90/10',
'B60L2230/00',
'Y02T 90/12',
'Y02T 90/14',
'Y02T 90/16',
'Y02T 90/167',
'Y02T 90/163'
)
--and ipc.IPC_CLASS_SYMBOL in ('H01M 6/00','H01M 2/00','H01M 8/00','H01M 10/00','H01M 12/00')
--and ipc.IPC_VALUE='I'
order by 1

I have a few questions following my attempt:
(1) I have a lot of duplicates where I see that the difference is the kind code. How do I draft a query where this won't happen, or do I need to clean it manually?
(2) What should I make out of the fact that most of the applications have no PUBLN_DATE, does this mean they were never published or a patent was not granted therefore the application was not published?
(3) Does the number 1 in PUBLN_FIRST_GRANT denotes for a publication post grant? If so, does the zero necessarily means the publication was at application stage?
(4) Why is my PUBLN_CLAIMS column empty? I want to find no. of claims of applications collected. Is this the wrong way or is there a problem with my query?
Thank you again, I appreciate your help on this.

I'm using the online PATSTAT autumn 2015.

Best,

Talya

[Geert Boedt]

Hello Talya,
Separate from the data catalog (which is rather reference material then study material), there are a number of good introductions and self-learning modules available on the PATSTAT download page:
http://www.epo.org/searching-for-patent ... html#tab13 The test database also has a number of working queries that explain basic queries.
With regards to various publication "kinds" (at the EPO) : please have a look at this link: http://www.epo.org/searching-for-patent ... lp.html#a1 .
Bottom line: 1 application will have at least 1 publication (an A1 or A2). If the patent is granted a B1 document will be published with the granted claims, so there will be at least 2 publications for a granted patent. Any statistics you do on patents need to take this into account. For some research you will count applications, sometimes families, exceptionally publications. In PATSTAT there is the tls201_appln table that contains bibliographical data with regards to the application, for example the application filing date, the application authority (where the patent was filed) etc... Each application will have at least 1 publication in the tls211_pat_publn table. If you make queries, you only should include tables which have attributes (data) which you need for your output (attributes in the SELECT clause) or attributes you need for your conditions (in the where clause). All selected attributes will be shown in the final table and depending on the number of tables joined, you will have multiple rows. Example:

Code: Select all

Select  app.appln_id ,app.appln_auth, app.appln_nr, app.appln_kind, app.appln_filing_date, cpc_class_symbol
from
TLS201_APPLN app,
TLS224_APPLN_CPC cpc
where 
app.appln_id=cpc.appln_id
and app.ipr_type='PI'
and app.granted=1
and app.appln_auth in ('DE','IL')
and cpc.CPC_CLASS_SYMBOL in (
'H01M  10/00',
'H01M   6/00',
'Y10S 977/948',
'Y02E  60/12',
'Y10S 903/903')
order by 1

If you run this query you will see that row 3 and 4 refer to the same application, but with the last column having 2 different CPC classification symbols. If you want to avoid this duplicate, you should remove the cpc_class_symbol from the SELECT clause and use a distinct or group by to de-duplicate. (You should in principle never clean manually if your query is complete and well structured.)
All applications have at least 1 publication with a publication date. There are no blank publication dates. (your query does not show the SELECT attributes, so it can not be run as-is.)
The publn_first_grant is an indication that the respective publication is the first public record that indicated that the application was granted. This indicator is also pre-aggregated to the attribute "granted" in the tls201_appln table. This allows users to immediately see if the patent was granted without need to join the publications table. If granted = 0 then the application was not granted (or EPO has not record that it was granted !)
PUBLN_CLAIMS column empty: we only have this data for EP and US applications. Kindly check the data catalog for this kind of information.