I am currently working on my thesis, where I identified a sample of firms. For each of those firms I need the patents and the corresponding backward and forward citations. My plan is to be able to create a patent portfolio for each firm that allows me to construct aggregated variables. Therefore I want a query that results in a list of patents from all of my firms.
I have 3 problems I am struggling with and some help would be really appreciated.
1. The query below does not seem to function, due to an error in the syntax. I cant seem to find error in it .....
(This query is not complete, in order to get faster results. For the final query I would leave out the WHERE restriction on country, the LIMIT to 10 and the I would also add all the names from my sample in a similar fashion)
SELECT f.appln_filing_year, f.appln_id, f.appln_filing_date, f.nb_citations, f.granted, d.person_id, d.doc_std_name, d.doc_std_name_id, a.*
FROM tls212_citation a
JOIN tls211_pat_publn b ON a.pat_publn_id = b.pat_publn_id
JOIN tls227_pers_publn c ON b.pat_publn_id = c.pat_publn_id
JOIN tls206_person d ON c.person_id = d.person_id
JOIN tls207_pers_appln e ON d.person_id = e.person_id
JOIN tls201_appln f ON e.appln_id = f.appln_id
WHERE d.person_ctry_code = 'AT'
AND f.ipr_type = 'PI'
AND (d.doc_std_name LIKE ‘%novartis%’
OR d.doc_std_name LIKE ‘%vaillant%’)
ORDER BY f.appln_filing_year
2. I am still missing the forward and backward citations for the patents, yet I do not know how to solve this issue, although I read the older posts about this topic in this forum. How can this be done?
3. My sample consists of US companies and due to the nature of my research it would be necessary to retrieve data ranging from around 1990 until today. Is such data available for the US?
(I am using PATSTAT Online)
It would really help me out a lot if someone is able to help me with some of my problems. Thank you in advance.
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.
1 post • Page 1 of 1