On code 1) Using t1.appln_id in (select appln_id from tls209_appln_ipc where ipc_class_symbol in ('F02C 1/05','F02C 1/06' ) ) will retrieve all the data (and all the IPC codes for all the applications) for wich there is an IPC code = F02C 1/05' or 'F02C 1/06'. If you only want those values to be in ...

Making a "count" on the attribute nb_citing_docdb_fam is not correct. nb_citing_docdb_fam already gives you the the number of forward citations, so I assume you want to make the sum. You also have to keep in mind that the nb_citing_docdb_fam is a number that is calculated at FAMILY level, ...

There is a typo in your SQL, and you have the appln_title in the SELECT part without having the tls202_appln_title in your joined tables. Also the Y-tags are part of the CPC scheme (table tls225_docdb_fam_cpc), and are not availalble in the IPC table. You have also joined the tls212 table, but you d...

Hello Kevin, Most applications will have more then one CPC classification code; and there is no uniform good way to assign one unique CPC classification symbol to one application (or family). (One could also question whether this is a good thing to do, because all classification codes have their pur...

Hello Xiaohan, that query creates a too heavy load on the server and it is automatic cancelled because it takes too much resources on the server. (There is a build in limitation to avoid the system working on a query for more then one hour.) I run it on an my local system and it took about 4 hours. ...

On the differences between the "coverage" document and the data. "Small" differences can occur; we have replenished in PATSTAT the missing citations, and the PDF document is created on the source data, not the PATSTAT data. We also de-duplicated the NPL in PATSTAT, which would le...

difficult to say, please post your SQL.

On your first query, you made a mistake to rename tls201_appln to a , but then still used the original name. Further you made a SUM of the appln_id's which should be a "count(distinct( case..... end)) as...." In the SELECT clause you can add as many columns as you want, based of course on ...

Hello Xiaohan, The reason why you don't find any result is because of the way your query is build using t2.ipc_class_symbol LIKE 'C10L 3/00' AND 'F02C 1/05'. This is correct SQL syntax, but it does not work for what you intend to do. SQL and relational data bases have what we call normalised data, s...

a COUNT( DISTINCT (tls201_appln.appln_id)) guarantees that each application is counted only one time. For this query, it does not make much difference, but in PATSTAT it is good practice to use DISTINCT to avoid unwanted double counting of patent applications. The query gives you the total number of...