multiple SQL queries, for loop

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

paolob
Posts: 3
Joined: Tue Aug 24, 2021 10:02 pm

multiple SQL queries, for loop

Post by paolob » Tue Aug 24, 2021 10:09 pm

Hello
trying to run multiple queries, each returning a single document.
I can do the single query but is it possible to concatenate many of them, for example to traverse a number of classification symbols with a for loop?
Many thanks for any suggestion!
Paolo


MartinK
Posts: 3
Joined: Tue Jun 29, 2021 7:39 am

Re: multiple SQL queries, for loop

Post by MartinK » Fri Aug 27, 2021 11:36 am

Dear Paolo,
Most SQL dialects have some procedural extensions where you can loop through a set of rows, processing one row at a time. In T-SQL (of MS SQL Server) there is the WHILE construct.

Having said that, please note:
  • These SQL extensions cannot be used in PATSTAT Online (which supports solely SELECT statements), but in your local PATSTAT database.
  • These procedural (a.k.a. imperative) SQL is typically much more inefficient compared to the set-based, algebraic SQL. Depending on your task, this may or may not be an issue.
  • I very rarely had the need to use procedural SQL, because (nested) SELECTs and built-in functions are powerful enough.
Best,
Martin


paolob
Posts: 3
Joined: Tue Aug 24, 2021 10:02 pm

Re: multiple SQL queries, for loop

Post by paolob » Wed Sep 01, 2021 3:11 pm

Hi Martin
thanks for the explanation!
I can live with multiple SELECTs, but I still have a problem when each contains an ORDER BY. For example I'd like to combine a few of the followings. However, using UNION throws an error "Incorrect syntax near the keyword 'ORDER'.

SELECT TOP 1 tls201_appln.nb_citing_docdb_fam, tls201_appln.appln_id, tls204_appln_prior.prior_appln_id, CONCAT(tls201_appln.appln_auth, tls201_appln.appln_nr, tls201_appln.appln_kind), appln_filing_date, earliest_filing_date, tls204_appln_prior.prior_appln_id
FROM tls201_appln JOIN tls224_appln_cpc
ON tls201_appln.appln_id = tls224_appln_cpc.appln_id
JOIN tls204_appln_prior
ON tls201_appln.appln_id = tls204_appln_prior.prior_appln_id
WHERE cpc_class_symbol = 'F02C 6/20'
ORDER BY nb_citing_docdb_fam DESC, earliest_filing_date ASC

Paolo


MartinK
Posts: 3
Joined: Tue Jun 29, 2021 7:39 am

Re: multiple SQL queries, for loop

Post by MartinK » Thu Sep 02, 2021 11:00 am

Hi Paolo,

I suppose you want to find the top application according to certain criteria per CPC symbol.

Here is a query using the ROW_NUMBER function. There are for sure alo other ways to achieve the same result:

Code: Select all

SELECT * 
from 
	(SELECT tls201_appln.nb_citing_docdb_fam,
    	tls201_appln.appln_id, 
		CONCAT(tls201_appln.appln_auth, tls201_appln.appln_nr, tls201_appln.appln_kind) as pretty_appln_nr, 
        appln_filing_date, 
        earliest_filing_date, 
		ROW_NUMBER() OVER (PARTITION BY cpc_class_symbol ORDER BY nb_citing_docdb_fam DESC, earliest_filing_date ASC) as rownr, 
    	cpc_class_symbol
	FROM tls201_appln 
	JOIN tls224_appln_cpc ON tls201_appln.appln_id = tls224_appln_cpc.appln_id
	JOIN tls204_appln_prior ON tls201_appln.appln_id = tls204_appln_prior.prior_appln_id
	WHERE (cpc_class_symbol IN ( 'F02C   5/10',  'F02C   6/10',  'F02C   6/20') 
	        OR  cpc_class_symbol LIKE 'A01B%'
	        )
	) temptable
WHERE rownr = 1
I made several changes:
  • CPC /IPC symbols must follow a certain structure, otherwise nothing will be found. I added spaces to make them conformant. Please check the Data Catalog for the correct CPC/IPC structure.
  • I added more CPC symbols for illustration purposes. You may use the IN function to retrieve complete symbols, or the LIKE comparison operator if you need wildcards.
  • In your query you consider only applications which are priorities, right? Applications which are not priorities will not be retrieved.
  • Your query retrieved 3 columns which all returned the application ID, so it returned 3 times the same value. I removed this redundancy.
  • Now the main logic: I used the ROW_NUMBER function or create a sequence number for each row, using CPC symbols to partition the row set. The inner SELECT statement (starting in line 3) does most of the logic. Just run the inner SELECT only and you will see. The inner SELECT creates a temporaryy table (here named "temptable"). Note that like for all tables all its attributes must have a unique name. So I added some attribute aliases to give them unique names.
  • The outer select is quite simple. It just returns all the rows of the inner table whose row number is 1. So you get 1 row for each CPC symbol.
Best,


paolob
Posts: 3
Joined: Tue Aug 24, 2021 10:02 pm

Re: multiple SQL queries, for loop

Post by paolob » Mon Sep 20, 2021 7:26 pm

Hi Martin
thanks a lot for the improvements. It works now. I've only added a column and all the symbols I needed, see below for reference of other users.

The only remaining issue is that a single very cited document might have multiple classes, so that it appears to be the most cited in each of these classes. It would probably be more meaningful to filter out the citations that are coming from a search report of a document that is not classified in the same class. A bit complicated to implement, I admit, since it would probably require looping through the top most cited documents (in each class) and checking the class of the citing document and then compiling a new ranking.

Paolo

Code: Select all

SELECT * 
from 
	(SELECT tls201_appln.nb_citing_docdb_fam, 
    	tls201_appln.appln_id, 
		CONCAT(tls201_appln.appln_auth, tls201_appln.appln_nr, tls201_appln.appln_kind) as pretty_appln_nr, 
        appln_filing_date, 
        earliest_filing_date, 
		ROW_NUMBER() OVER (PARTITION BY cpc_class_symbol ORDER BY nb_citing_docdb_fam DESC, earliest_filing_date ASC) as rownr, 
    	cpc_class_symbol,
        CONCAT (publn_auth, publn_nr) as PublNr,
        publn_auth
	FROM tls201_appln 
	JOIN tls224_appln_cpc ON tls201_appln.appln_id = tls224_appln_cpc.appln_id
	JOIN tls204_appln_prior ON tls201_appln.appln_id = tls204_appln_prior.prior_appln_id
   	JOIN tls211_pat_publn ON tls201_appln.appln_id = tls211_pat_publn.appln_id
	WHERE (cpc_class_symbol IN ( 'F02C   1/00', 'F02C   1/002', 'F02C   1/005', 'F02C   1/007', 'F02C   1/02', 'F02C   1/04', 'F02C   1/05', 'F02C   1/06', 'F02C   1/08', 'F02C   1/10', 'F02C   1/105', 'F02C   3/00', 'F02C   3/02', 'F02C   3/04', 'F02C   3/045', 'F02C   3/05', 'F02C   3/055', 'F02C   3/06', 'F02C   3/062', 'F02C   3/064', 'F02C   3/067', 'F02C   3/073', 'F02C   3/08', 'F02C   3/085', 'F02C   3/09', 'F02C   3/10', 'F02C   3/103', 'F02C   3/107', 'F02C   3/113', 'F02C   3/13', 'F02C   3/14', 'F02C   3/145', 'F02C   3/16', 'F02C   3/165', 'F02C   3/20', 'F02C   3/205', 'F02C   3/22', 'F02C   3/24', 'F02C   3/26', 'F02C   3/28', 'F02C   3/30', 'F02C   3/305', 'F02C   3/32', 'F02C   3/34', 'F02C   3/36', 'F02C   3/365', 'F02C   5/00', 'F02C   5/02', 'F02C   5/04', 'F02C   5/06', 'F02C   5/08', 'F02C   5/10', 'F02C   5/11', 'F02C   5/12', 'F02C   6/00', 'F02C   6/003', 'F02C   6/006', 'F02C   6/02', 'F02C   6/04', 'F02C   6/06', 'F02C   6/08', 'F02C   6/10', 'F02C   6/12', 'F02C   6/14', 'F02C   6/16', 'F02C   6/18', 'F02C   6/20', 'F02C   6/203', 'F02C   6/206', 'F02C   7/00', 'F02C   7/04', 'F02C   7/042', 'F02C   7/045', 'F02C   7/047', 'F02C   7/05', 'F02C   7/052', 'F02C   7/055', 'F02C   7/057', 'F02C   7/06', 'F02C   7/08', 'F02C   7/10', 'F02C   7/105', 'F02C   7/12', 'F02C   7/125', 'F02C   7/14', 'F02C   7/141', 'F02C   7/143', 'F02C   7/1435', 'F02C   7/16', 'F02C   7/18', 'F02C   7/185', 'F02C   7/20', 'F02C   7/22', 'F02C   7/222', 'F02C   7/224', 'F02C   7/228', 'F02C   7/232', 'F02C   7/236', 'F02C   7/2365', 'F02C   7/24', 'F02C   7/25', 'F02C   7/26', 'F02C   7/262', 'F02C   7/264', 'F02C   7/266', 'F02C   7/268', 'F02C   7/27', 'F02C   7/272', 'F02C   7/275', 'F02C   7/277', 'F02C   7/28', 'F02C   7/30', 'F02C   7/32', 'F02C   7/36', 'F02C   9/00', 'F02C   9/16', 'F02C   9/18', 'F02C   9/20', 'F02C   9/22', 'F02C   9/24', 'F02C   9/26', 'F02C   9/263', 'F02C   9/266', 'F02C   9/28', 'F02C   9/285', 'F02C   9/30', 'F02C   9/32', 'F02C   9/34', 'F02C   9/36', 'F02C   9/38', 'F02C   9/40', 'F02C   9/42', 'F02C   9/44', 'F02C   9/46', 'F02C   9/48', 'F02C   9/50', 'F02C   9/52', 'F02C   9/54', 'F02C   9/56', 'F02C   9/58') 
	        )
	) temptable
WHERE rownr = 1


Post Reply