Backward and forward citations; Problems with syntax

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

lukassbe
Posts: 2
Joined: Fri Jul 18, 2014 4:41 pm

Backward and forward citations; Problems with syntax

Post by lukassbe » Thu Jul 24, 2014 10:08 am

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
LIMIT 10

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.

Lukas


Post Reply