EPO - Discussion forums

Hi,
I have some questions about the description of DATA CATALOG PATSTAT manual.

First of all,
I feel confused about "pat_publn_id" and "publn_id". Because I did not find any column called "publn_id", I only found "pat_publn_id" in tls211_pat_publn table.
Is column "publn_id" equal to column "pat_publn_id" ?

Besides, I have a question about an example demonstrated in page 25 (DATA CATALOG PATSTAT manual 2015 Spring Edition Version 5.03).
Here is the example: the PUBLN_ID 20194422 identified the application FR833507D with kind code A. Using business identifiers, you will need 3 attributes to uniquely identify this application: PUBLN_AUTH = FR, PUBLN_NR=833507D and APPLN_KIND=A.

I tried to find pat_publn_id =20194422(select * from tls211_pat_publn WHERE pat_publn_id='20194422') , and I found nothing.
However, I used the following SQL, and I found that number 20194422 is actually the appln_id.
SELECT * from tls211_pat_publn t1
inner join tls201_appln t2 on t1.appln_id=t2.appln_id
where t1.publn_auth = 'FR' and t1.publn_nr='833507' and t2.appln_kind='A'

The data I found from the example should be APPLN_ID=20194422, APPLN_AUTH = FR, APPLN_NR=833507D, and APPLN_KIND=A.

Why PATSTAT manual uses Publn_id to describe appln_id?
Is the example mis-written ?
Or it has other reasons?


Thank you!!!!

Best regards,
Renee

Short answer: The example is wrong. It should be the appln_id. There is no "publn_id" variable.

In earlier versions of PATSTAT the appln_id was not constant, and you would need the three fields appln_auth, appln_nr and appln_kind to be able to match applications between versions. In some old documentation they gave this warning: "Take care ! In the PATSTAT April 2006 edition, the surrogate key appln_id for FR 833507D A was not 20,337,703 - it was 17,252,512."
That same application now has appln_id 20194422 and it will have the same appln_id in the next version of PATSTAT. So you no longer need the three fields described above to uniquely identify an application across versions... except when for some reason you are using an old version of PATSTAT.

Renee,
Thank you for reporting this typo.

The paragraph you mentioned in section 4.3.1 in the PATSTAT's Data Catalog should correctly read (Change is marked in red):

The advantage of a surrogate key is that it is generally easier and more efficient to identify entities or to join tables with a surrogate key than with business identifiers.
As an example: The APPLN_ID 20194422 identified the application FR833507D with kind code A. Using business identifiers, you will need 3 attributes to uniquely identify this application: PUBLN_AUTH = FR, PUBLN_NR=833507D and APPLN_KIND=A. Also, from a technical point of view, the database can store and manage surrogate keys more efficiently, which results in smaller database sizes and faster queries.

This will be corrected in the next version of the Data Catalog.
Although I take great care and always ask colleagues to proofread, it's likely that there are more mistakes hidden in this comprehensive document. You may report errors and suggestions for improvement any time to our help desk email address which also is given in the Data Catalog.

Dear mkracker and nico.rasters,

I really appreciate your reply

Best regards,

Renee