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.
-
Amleto
- Posts: 2
- Joined: Wed Nov 01, 2017 6:31 pm
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
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
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
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