Accessing Data from ER Register PATSTAT Online

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

stefana.vasilache
Posts: 3
Joined: Wed May 06, 2020 2:09 pm

Accessing Data from ER Register PATSTAT Online

Post by stefana.vasilache » Wed May 06, 2020 2:25 pm

Hello,

I am currently working on my MSc thesis and i am trying to interrogate the EP Register from PATSTAT Online to retrieve a list of patent applications' register number and the inventor's and applicant's country, but i receive the following error:

"[SELECT - 0 row(s), 0 secs] [Error Code: 8156, SQL State: S0001] The column 'id' was specified multiple times for 'applicant'."

I don't know what i am doing wrong.. Can someone help me please? Thank you. :)

Here it is the SQL query that i run:

Code: Select all

SELECT distinct appln_auth, appln_nr, appln_filing_date
	  , inventor.country Inventor_country, applicant.country Applicant_country
FROM reg101_appln join reg110_title on reg101_appln.id = reg110_title.id
	join (SELECT distinct reg110_title.id, reg107_parties.id, reg107_parties.country 
			FROM reg107_parties join reg110_title 
			on reg107_parties.bulletin_year = reg110_title.bulletin_year and reg107_parties.bulletin_nr = reg110_title.bulletin_nr and reg107_parties.type like 'A' ) applicant 
	on reg101_appln.id = applicant.id

	join (SELECT distinct reg110_title.id, reg107_parties.id, reg107_parties.country 
			FROM reg107_parties join reg110_title 
			on reg107_parties.bulletin_year = reg110_title.bulletin_year and reg107_parties.bulletin_nr = reg110_title.bulletin_nr and reg107_parties.type like 'I' ) inventor
	on reg101_appln.id = inventor.id
    
where reg101_appln.appln_filing_date >= '2014/01/01' and  reg101_appln.appln_filing_date <= '2020/04/30' and inventor.country like 'RO' and applicant.country like 'RO' 


mkracker
Posts: 114
Joined: Wed Sep 04, 2013 6:17 am
Location: Vienna

Re: Accessing Data from ER Register PATSTAT Online

Post by mkracker » Thu May 07, 2020 7:19 am

Hi Stefana,

This line in your nested SELECT is causing the error:
"SELECT distinct reg110_title.id, reg107_parties.id, reg107_parties.country "

The result of this SELECT is a (virtual) table containing 2 columns named "id" which is never allowed because they cannot be referred to unambiguously. Instead, you can give the columns aliases, like
"SELECT distinct reg110_title.id AS TITLE_ID, reg107_parties.id AS PARTY_ID, reg107_parties.country "

May I give more feedback?
  • I do not understand why you involve the title table here. But probably you you want to do more than you described in your problem statement.
  • Your query puts applicant and inventor countries in one record. Assuming that you have 2 applicants with different countries and 6 inventors, also with different countries, you will get 2x6 = 12 rows, one for each applicant country and inventor country combination.
A simpler query which just does what you described in your problem statement could be like this:

Code: Select all

SELECT DISTINCT appln_auth, appln_nr, appln_filing_date,
CASE type 
	WHEN  'A' THEN 'Applicant Country'
	WHEN  'I' THEN 'Inventor Country'
END AS country_type,
country
FROM reg101_appln a
JOIN reg107_parties p on a.id = p.id
WHERE type IN ('A', 'I') and appln_filing_date = '2015-04-01'
ORDER BY appln_auth, appln_nr, appln_filing_date, country_type, country
Please note that applicant and inventors may change over time, and my script just considers all of them. However, the data allows you to precisely analyse the history of applicants,inventors and legal representative, in case you need to.
-------------------------------------------
Martin Kracker / EPO


stefana.vasilache
Posts: 3
Joined: Wed May 06, 2020 2:09 pm

Re: Accessing Data from ER Register PATSTAT Online

Post by stefana.vasilache » Thu May 07, 2020 7:37 am

Hello,

Thank you so much for the feedback. I was involving the title table because i considered that table to be a link table between reg101_appln and reg107_parties, but i was wrong. (i considered the field bulletin_year a FK, but now i see that i just should have used the id field )

Now that i see your query, the things are much clearer to me.


Post Reply