Getting IPC codes of citing patents

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

Amleto
Posts: 2
Joined: Wed Nov 01, 2017 6:31 pm

Getting IPC codes of citing patents

Post by Amleto » Wed Nov 01, 2017 6:53 pm

Hi there, I am trying to include the IPC codes of the citing patents to my table. My table from this initial code gives me information of all patents under G01S, plus their citations.

Code: Select all

SELECT 
	CITED.PUBLN_AUTH, 
	CITED.PUBLN_NR, 
	CITED.PUBLN_KIND,
	T1.IPC_CLASS_SYMBOL,
	T2.APPLN_TITLE, 
	CITING.PUBLN_AUTH, 
	CITING.PUBLN_NR, 
	CITING.PUBLN_KIND
FROM TLS211_PAT_PUBLN AS CITING 
	JOIN TLS212_CITATION ON CITING.PAT_PUBLN_ID = TLS212_CITATION.PAT_PUBLN_ID
	JOIN TLS211_PAT_PUBLN AS CITED ON TLS212_CITATION.CITED_PAT_PUBLN_ID = CITED.PAT_PUBLN_ID 
	JOIN TLS201_APPLN ON CITED.APPLN_ID = TLS201_APPLN.APPLN_ID
	JOIN TLS209_APPLN_IPC AS T1 ON TLS201_APPLN.APPLN_ID = T1.APPLN_ID
	JOIN TLS202_APPLN_TITLE AS T2 ON TLS201_APPLN.APPLN_ID = T2.APPLN_ID
		WHERE CITED.PUBLN_AUTH='US' 
		AND T1.IPC_CLASS_SYMBOL LIKE "G01S%"
Now, I would like to add to this table the IPCs and titles of the citing patents. So I have included a few more lines in the code above and came up with the following code, although, it doesn't work.

Code: Select all

SELECT 
	CITED.PUBLN_AUTH, 
	CITED.PUBLN_NR, 
	CITED.PUBLN_KIND,
	T1.IPC_CLASS_SYMBOL,
	T2.APPLN_TITLE, 
	CITING.PUBLN_AUTH, 
	CITING.PUBLN_NR, 
	CITING.PUBLN_KIND,
	#T3.IPC_CLASS_SYMBOL,
	#T4.APPLN_TITLE 
FROM TLS211_PAT_PUBLN AS CITING 
	JOIN TLS212_CITATION ON CITING.PAT_PUBLN_ID = TLS212_CITATION.PAT_PUBLN_ID
	#JOIN TLS201_APPLN ON CITING.APPLN_ID = TLS201_APPLN.APPLN_ID
	#JOIN TLS209_APPLN_IPC AS T3 ON TLS201_APPLN.APPLN_ID = T3.APPLN_ID
	#JOIN TLS202_APPLN_TITLE AS T4 ON TLS201_APPLN.APPLN_ID = T4.APPLN_ID
	JOIN TLS211_PAT_PUBLN AS CITED ON TLS212_CITATION.CITED_PAT_PUBLN_ID = CITED.PAT_PUBLN_ID 
	JOIN TLS201_APPLN ON CITED.APPLN_ID = TLS201_APPLN.APPLN_ID
	JOIN TLS209_APPLN_IPC AS T1 ON TLS201_APPLN.APPLN_ID = T1.APPLN_ID
	JOIN TLS202_APPLN_TITLE AS T2 ON TLS201_APPLN.APPLN_ID = T2.APPLN_ID
		WHERE CITED.PUBLN_AUTH='US' 
		AND T1.IPC_CLASS_SYMBOL LIKE "G01S%"


Does anybody have any idea on how to fix this code and to link the IPC codes of the citing patents?

Many thanks


Geert Boedt
Posts: 176
Joined: Tue Oct 19, 2004 10:36 am
Location: Vienna

Re: Getting IPC codes of citing patents

Post by Geert Boedt » Thu Nov 02, 2017 5:13 pm

Are you using PATSTAT Online, or do you have a local installation ?
This kind of "output" where data has to be de-normalised is rather difficult to obtain from a 1 go SQL query.
Unless you don't mind having multiple "rows" for 1 citing application. (one for each possible data occurrence)
A local installation with intermediate tables would make it easier.
Best regards,

Geert Boedt
PATSTAT support
Business Use of Patent Information
EPO Vienna


Amleto
Posts: 2
Joined: Wed Nov 01, 2017 6:31 pm

Re: Getting IPC codes of citing patents

Post by Amleto » Thu Nov 02, 2017 6:46 pm

Yes, I am using PATSTAT Online. No I don't mind having the multiple rows for each citation application.
Do you know how to make it work on the version online?


Geert Boedt
Posts: 176
Joined: Tue Oct 19, 2004 10:36 am
Location: Vienna

Re: Getting IPC codes of citing patents

Post by Geert Boedt » Thu Nov 23, 2017 7:14 pm

Hello Amleto,
See SQL query below.
I have limited the set by only taking citing documents published in 2015. Otherwise you will get millions of records (Cartesian product of all citing publications with all cited publications with all IPC codes from the citing publication ...)

Code: Select all

SELECT distinct
	CITED.PUBLN_AUTH +CITED.PUBLN_NR+CITED.PUBLN_KIND,
	left(T1.IPC_CLASS_SYMBOL,4) IPC_cited,
	T2.APPLN_TITLE, 
	CITING.PUBLN_AUTH +	CITING.PUBLN_NR+CITING.PUBLN_KIND,
    citing.publn_date,
    CITING_IPC.IPC_CLASS_SYMBOL
  FROM TLS211_PAT_PUBLN AS CITING 
	JOIN TLS212_CITATION ON CITING.PAT_PUBLN_ID = TLS212_CITATION.PAT_PUBLN_ID
	JOIN TLS211_PAT_PUBLN AS CITED ON TLS212_CITATION.CITED_PAT_PUBLN_ID = CITED.PAT_PUBLN_ID 
	JOIN TLS201_APPLN tls201a ON CITED.APPLN_ID = tls201a.APPLN_ID
	JOIN TLS209_APPLN_IPC AS T1 ON TLS201a.APPLN_ID = T1.APPLN_ID
	JOIN TLS202_APPLN_TITLE AS T2 ON TLS201a.APPLN_ID = T2.APPLN_ID
   JOIN tls209_appln_ipc CITING_IPC on citing.appln_id =  CITING_IPC.APPLN_ID
		WHERE CITED.PUBLN_AUTH='US' 
		AND T1.IPC_CLASS_SYMBOL LIKE 'G01S%'
        AND year(citing.publn_date) = 2015
        order by  citing.publn_date asc, CITING.PUBLN_AUTH +	CITING.PUBLN_NR+CITING.PUBLN_KIND,
        CITED.PUBLN_AUTH +CITED.PUBLN_NR+CITED.PUBLN_KIND, 
   		CITING_IPC.IPC_CLASS_SYMBOL
For purpose of statistics, this might be a representative enough sample to do your research. If you really need to analyse the whole population (technology flows over the years...) , then you will need to install PATSTAT on a local server.
Best regards,

Geert Boedt
PATSTAT support
Business Use of Patent Information
EPO Vienna


Post Reply