TLS229_APPL_NACE2

[Christelle Olsommer]

Hi all,
I am a new user of PATSTAT and I need to extract patent data in the NACE2 codification. I have 3 questions :
1. I already find the Eurostat guide concerning the Concordance IPC V8 – NACE REV.2 but the problem I encounter is that there is no concordance for 4 digits NACE codes. Please could you send me a 4 digit detailed and updated NACE-IPC concordance table that I could use to do my queries?
2. 1. The main problem I encounter is to create and insert the section for the NACE sectors in SQL : please could you give me an example or model for extracting data with the NACE codes (2 sectors at least) with the TLS229_APPLN_NACE2 (4 digits details), please (in SQL language)?
3. How could I use the "weight" function in order to not have redundancies in my final results that are classified with the NACE codification?
Thank you in advance for your help
Best regards
Christelle

[EPO / PATSTAT Support]

Hello Christelle,

On (1) the "concordance table" is part of PATSTAT under the LTS902_IPC_NACE table.
If you run a query like the one below, you will retrieve all records, wich you can download if you would like to do so.

Code: Select all

SELECT ipc
      ,not_with_ipc
      ,unless_with_ipc
      ,nace2_code
      ,nace2_weight
      ,nace2_descr
  FROM tls902_ipc_nace2
  order by ipc

You have to keep in mind that the "concordance table" is not a straightforward IPC(4-digit)-NACE table. For some IPC codes there was a need to go to the main group level and for some IPC codes a % distribution (weighing) had to be implemented in order to be truthful to the model. Additionally some codes are assigned on the total "IPC picture", so you can not straightforward link 4digits to NACE without taking the above in account.
On (2) to make it easy for PATSTAT users, we have used the tls902 table to create a ready table directly linked to the application table (tls201). So there should be no further need to process the data, except for writing the query. Here is a query that shows the NACE code +description for a singel application:

Code: Select all

Select distinct appln_auth 
      , appln_nr 
      , appln_kind 
      , appln_filing_date 
      , appln_filing_year 
	  ,tls229_appln_nace2.nace2_code
      ,tls229_appln_nace2.weight
	  ,tls902_ipc_nace2.nace2_descr
from tls201_appln join tls229_appln_nace2 on tls201_appln.appln_id = tls229_appln_nace2.appln_id
join tls902_ipc_nace2 on tls229_appln_nace2.nace2_code = tls902_ipc_nace2.nace2_code
where tls201_appln.appln_auth = 'EP'and appln_nr = '08010677'

Assume for example that you want to count how many applications have been filed at the EPO in 2016, distributed over the NACE codes. (Keeping in mind that 1 application can be part of multiple nace codes, so your total over all NACE codes will be more then the number of applications filed):

Code: Select all

Select distinct tls902_ipc_nace2.nace2_descr
	  ,tls229_appln_nace2.nace2_code
      ,count(distinct (tls201_appln.appln_id)) total
from tls201_appln join tls229_appln_nace2 on tls201_appln.appln_id = tls229_appln_nace2.appln_id
join tls902_ipc_nace2 on tls229_appln_nace2.nace2_code = tls902_ipc_nace2.nace2_code
where tls201_appln.appln_auth = 'EP' and appln_filing_year = 2016
group by tls902_ipc_nace2.nace2_descr
	  ,tls229_appln_nace2.nace2_code
	  order by total desc

On(3) And if you want to use the weighing:

Code: Select all

SELECT tls229_appln_nace2.nace2_code, nace2_descr
    ,sum (weight)/total.app * 100 procent
FROM tls229_appln_nace2 join tls201_appln on tls229_appln_nace2.appln_id = tls201_appln.appln_id
  join (select distinct nace2_code, nace2_descr from tls902_ipc_nace2) nace on tls229_appln_nace2.nace2_code = nace.nace2_code,
  (Select count(distinct (tls201_appln.appln_id))app  from tls201_appln join tls229_appln_nace2 on tls201_appln.appln_id = tls229_appln_nace2.appln_id
	 where appln_auth = 'EP' and appln_filing_year = 2016) total
where appln_auth  = 'EP' and appln_filing_year = 2016
group by tls229_appln_nace2.nace2_code, nace2_descr,total.app
order by Procent desc

The only purpose of the table "Total" is to count how many EP applications filed in 2016 have a NACE code... 136.403

[Christelle Olsommer]

Thank you very much for the prompt reply.
I have an additional question: is it possible to count the applications from a given country in a given NACE class, please? More precisely can you send me an example of query, please (because I have to extract all applications for a give country a given year)?

[EPO / PATSTAT Support]

Hello Christelle,
yes of course.
It's a variation on the second query.
Here is an example that gives you a patent count per Nace code for all applications filed in 2016 at the French patent office. (no including PCT applications filed at INPI).

Code: Select all

Select distinct tls902_ipc_nace2.nace2_descr
	  ,tls229_appln_nace2.nace2_code
      ,count(distinct (tls201_appln.appln_id)) total
from tls201_appln join tls229_appln_nace2 on tls201_appln.appln_id = tls229_appln_nace2.appln_id
join tls902_ipc_nace2 on tls229_appln_nace2.nace2_code = tls902_ipc_nace2.nace2_code
where tls201_appln.appln_auth = 'FR' and appln_filing_year = 2015
group by tls902_ipc_nace2.nace2_descr
	  ,tls229_appln_nace2.nace2_code
	  order by total desc

If you want to narrow down to one NACE code then you simply adapt the WHERE clause with an extra limitation.

Code: Select all

Select distinct tls902_ipc_nace2.nace2_descr
	  ,tls229_appln_nace2.nace2_code
      ,count(distinct (tls201_appln.appln_id)) total
from tls201_appln join tls229_appln_nace2 on tls201_appln.appln_id = tls229_appln_nace2.appln_id
join tls902_ipc_nace2 on tls229_appln_nace2.nace2_code = tls902_ipc_nace2.nace2_code
where tls201_appln.appln_auth = 'FR' and appln_filing_year = 2015 and tls902_ipc_nace2.nace2_code = '26.1'
group by tls902_ipc_nace2.nace2_descr
	  ,tls229_appln_nace2.nace2_code
	  order by total desc

[Christelle Olsommer]

Thank you very much for your precious help.
Unfortunately I have an error message for the second query as describe below:
‎10‎:‎46‎:‎26 [SELECT - 82 row(s), 6.196 secs, server #0] Result set fetched
‎10‎:‎46‎:‎28 [Result table, 0.632 secs, server #0] Data fetched
‎10‎:‎46‎:‎49 [Result table, 0.023 secs, server #0] Data fetched
‎10‎:‎47‎:‎24 [Result table, 0.039 secs, server #0] Data fetched
‎10‎:‎55‎:‎04 [SELECT - 0 row(s), 0 secs] [Error Code: 102, SQL State: S0001] Incorrect syntax near '26.1'.
‎10‎:‎55‎:‎15 [SELECT - 0 row(s), 0 secs] [Error Code: 156, SQL State: S0001] Incorrect syntax near the keyword 'order'.
‎10‎:‎56‎:‎46 [SELECT - 1 row(s), 1.552 secs, server #0] Result set fetched

Please could you send me a rectified query.
Thank you in advance for your precious help
Christelle