Coding with Boolean operators for IPC codes

[annina]

Hey there,

I am currently doing research for my master thesis and I was hoping you could help me with some coding because I struggle to where I want to get to.
Basically the idea was to look at water-related ICT patents to understand the technological development, patent patterns etc.
Now I already identified from the OECD all IPC codes which are linked to ICT (https://www.oecd.org/sti/inno/40807441.pdf) and I currently investigate how to do this for the water sector (possibilties are NACE2 codes, other ICT codes or similar proposals).
So I will need to do 2 sub-queries saying internally "or" and then link them saying "and" because the relevant patents should have at least one attribute of each group.

Now I saw that with Patstat online, one can do this either with coding like
1. ipc_class_symbol = 'XXX' or ipc_class....
2. ipc_class_symbol in ('XXX', 'YYY',...)
or 3. ipc_class_symbol like 'XXX%' or ipc_class...
Given that I have 64 IPC codes for the ICT part alone, I wanted to enquire what the best format is because the cost can get to high and so I dont get any result.

I also have got some other restrictions such as
where ipr_type = 'PI'
and appln_kind != 'D'
and appln_filing_year < 9999
and I will need to join a few lists (such as 201, 209, 207, 206, 211 etc).

I hope you can give me some advice, which will be very appreciated from my side.
Many thanks
Anna

[Geert Boedt]

Dear Anna,
my approach would be the following: first you make the query that select all the appln_id's from the patent applications that fulfill your criteria. Then you extract a PATSTAT sample in MS ACCESS that contains all the tables which you need for for your analysis. I mean, if you only need tls209 to get to the IPC codes in order to reduce your sample, then there is no real need to export that table unless you need it for further analysis. Joining all tables will indeed increase the "cost" factor and might block the query from being executed all together. So here is a sample query that combines a set of IPC classes (with various lengths) with the Y02W CPC classification - wastewater treatement. I only "selected" distinct appln_id's because that is the only attribute I need to make a data extraction in PATSTAT Online. Observe the brackets around the grouped OR conditions.

Code: Select all

select distinct tls201_appln.appln_id from tls209_appln_ipc join tls201_appln on tls209_appln_ipc.appln_id = tls201_appln.appln_id
where (left (ipc_class_symbol,4) in ('G11B','H03F','H03G','H03J','H04H','H04N','H04R','H04S', 'B07C','B41J','B41K','G02F','G03G','G05F','G09G','G10L','G11C','H03K','H03L','G01B','G01C', 
'G01D','G01F','G01G','G01H','G01J','G01K','G01L','G01M','G01N','G01P','G01R','G01V','G01W',
'G05B','G08G','G09B','H01B','H01J','H01L')
OR left (ipc_class_symbol,3) in ('G06','G07')
OR left (ipc_class_symbol,8) = 'G02B   6')
AND tls201_appln.appln_id in (select distinct appln_id from tls224_appln_cpc where left(cpc_class_symbol,4) = 'Y02W' )
AND ipr_type = 'PI' and appln_kind != 'D' and appln_filing_year < 9999

Your definition "water" is too vague to give a concrete example, but one way or another you need to reduce your final sample to <100.000 applications in order to download a set. Have a look at the "where" condition in the above query where I used a SUBQUERY SELECT with the IN function; it forces patents to have the IPC-ICT classes as well as being classified in Y02W at the same time. A simple OR would increase the sample instead of decreasing it. I hope this is usefull.

[annina]

Dear Geert,

thanks so much for your answer. In the meanwhile (I posted the topic already last week in a different forum, but only realised yesterday that it was the wrong one), I solved the query with an "intersect" (see below). Not sure of you think that is a good way to do it or not...I chose the "like" statement because I would like to use the %-Wildcard as there are often subcodes whch apply as well to my research. If I understand correctly, "in" does not allow that right?
I have identified all water codes via different OECD publications with IPC/CPC codes.
I got around 30,000 patents via 3 different queries (IPC-IPC, IPC-IPC-IPC and IPC-CPC) and yes, I need to link several tables (8 to 10) in order to do the statistical analysis...with which I struggle now.
I added left outer joins to connect other tables and it worked fine for the IPC-CPC query (using table alias lik a.*, i.*, p.* etc. ) but when I try to do the same with the IPC-IPC tables, it doesnt work: I get 0 results again as the outcome...how can I manage to do it? (If I do it without linking the other tables, then it works)
Of course for me it's enough to extract all data and then I will work in SAS with it to do the statistical analysis.

I added this piece in below 3 queries to join the tables:
"left outer join tls204_appln_prior pr on a.appln_id = pr.appln_id
left outer join tls207_pers_appln pa on a.appln_id = pa.appln_id
left outer join tls206_person p on pa.person_id = p.person_id
left outer join tls229_appln_nace2 n on a.appln_id = n.appln_id"

1. ICT-Water IPC sample (2 conditions, 28092 applications)
SELECT a.*
FROM tls201_appln a JOIN tls209_appln_ipc i
ON a.appln_id =i.appln_id /*join base and IPC table together*/
where ipr_type = 'PI'
and appln_kind != 'D'
and appln_filing_year < 9999
and ipc_class_symbol like ('B07C%') or ipc_class_symbol like ('B41J%') or ipc_class_symbol like ('B41K%') or ipc_class_symbol like ('G01B%') or ipc_class_symbol like ('G01C%') or ipc_class_symbol like ('G01D%') or ipc_class_symbol like ('G01F%') or ipc_class_symbol like ('G01G%') or ipc_class_symbol like ('G01H%') or ipc_class_symbol like ('G01J%') or ipc_class_symbol like ('G01K%') or ipc_class_symbol like ('G01L%') or ipc_class_symbol like ('G01M%') or ipc_class_symbol like ('G01N%') or ipc_class_symbol like ('G01P%') or ipc_class_symbol like ('G01R%') or ipc_class_symbol like ('G01S%') or ipc_class_symbol like ('G01V%') or ipc_class_symbol like ('G01W%') or ipc_class_symbol like ('G02F%') or ipc_class_symbol like ('G03G%') or ipc_class_symbol like ('G05B%') or ipc_class_symbol like ('G05F%') or ipc_class_symbol like ('G06%') or ipc_class_symbol like ('G07%') or ipc_class_symbol like ('G08C%') or ipc_class_symbol like ('G08G%') or ipc_class_symbol like ('G09B%') or ipc_class_symbol like ('G09C%') or ipc_class_symbol like ('G09G%') or ipc_class_symbol like ('G10L%') or ipc_class_symbol like ('G11B%') or ipc_class_symbol like ('G11C%') or ipc_class_symbol like ('H01L%') or ipc_class_symbol like ('H01P%') or ipc_class_symbol like ('H01Q%') or ipc_class_symbol like ('H03B%') or ipc_class_symbol like ('H03C%') or ipc_class_symbol like ('H03D%') or ipc_class_symbol like ('H03HF%') or ipc_class_symbol like ('H03G%') or ipc_class_symbol like ('H03H%') or ipc_class_symbol like ('H03J%') or ipc_class_symbol like ('H03K%') or ipc_class_symbol like ('H03L%') or ipc_class_symbol like ('H03M%') or ipc_class_symbol like ('H04B%') or ipc_class_symbol like ('H04H%') or ipc_class_symbol like ('H04J%') or ipc_class_symbol like ('H04K%') or ipc_class_symbol like ('H04L%') or ipc_class_symbol like ('H04M%') or ipc_class_symbol like ('H04N%') or ipc_class_symbol like ('H04Q%') or ipc_class_symbol like ('H04R%') or ipc_class_symbol like ('H04S%') or ipc_class_symbol like ('G02B 6%') or ipc_class_symbol like ('H01B 11%') or ipc_class_symbol like ('H01J 11%') or ipc_class_symbol like ('H01J 13%') or ipc_class_symbol like ('H01J 15%') or ipc_class_symbol like ('H01J 17%') or ipc_class_symbol like ('H01J 19%') or ipc_class_symbol like ('H01J 21%') or ipc_class_symbol like ('H01J 23%') or ipc_class_symbol like ('H01J 25%') or ipc_class_symbol like ('H01J 27%') or ipc_class_symbol like ('H01J 29%') or ipc_class_symbol like ('H01J 31%') or ipc_class_symbol like ('H01J 33%') or ipc_class_symbol like ('H01J 40%') or ipc_class_symbol like ('H01J 41%') or ipc_class_symbol like ('H01J 43%') or ipc_class_symbol like ('H01J 45%') or ipc_class_symbol like ('H01S 3%') or ipc_class_symbol like ('H01S 5%')
intersect
SELECT a*
FROM tls201_appln a JOIN tls209_appln_ipc i
ON a.appln_id =i.appln_id /*join base and IPC table together*/
where ipr_type = 'PI'
and appln_kind != 'D'
and appln_filing_year < 9999
and ipc_class_symbol like ('A01G 25%') or ipc_class_symbol like ('A47K 11/02%') or ipc_class_symbol like ('A47K 11/12%') or ipc_class_symbol like ('B63B 35/32%') or ipc_class_symbol like ('B63H 19/02%') or ipc_class_symbol like ('B63H 19/04%') or ipc_class_symbol like ('B63J 4%') or ipc_class_symbol like ('C02F%') or ipc_class_symbol like ('C05F 7%') or ipc_class_symbol like ('C09K 3/32%') or ipc_class_symbol like ('C12N 15/82%') or ipc_class_symbol like ('E02B%') or ipc_class_symbol like ('E03%') or ipc_class_symbol like ('F01D 11%') or ipc_class_symbol like ('F01K 23/08%') or ipc_class_symbol like ('F01K 23/10%') or ipc_class_symbol like ('F03B 3%') or ipc_class_symbol like ('F03B 7%') or ipc_class_symbol like ('F03B 13%') or ipc_class_symbol like ('F03B 15%') or ipc_class_symbol like ('F03C%') or ipc_class_symbol like ('F03G 7/04%') or ipc_class_symbol like ('F03G 7/05%') or ipc_class_symbol like ('F16K 21%') or ipc_class_symbol like ('F16L 55/07%')

2. ICT-Water-sample IPC 2 (3 conditions, 451 applications to join to above result)
SELECT a*
FROM tls201_appln a JOIN tls209_appln_ipc i
ON a.appln_id =i.appln_id /*join base and IPC table together*/
where ipr_type = 'PI'
and appln_kind != 'D'
and appln_filing_year < 9999
and ipc_class_symbol like ('E03%')
intersect
SELECT a*
FROM tls201_appln a JOIN tls209_appln_ipc i
ON a.appln_id =i.appln_id /*join base and IPC table together*/
where ipr_type = 'PI'
and appln_kind != 'D'
and appln_filing_year < 9999
and ipc_class_symbol like ('F16L 55/16%') or ipc_class_symbol like ('F17D 5%') or ipc_class_symbol like ('G01M 3/08%') or ipc_class_symbol like ('G01M 3/14%') or ipc_class_symbol like ('G01M 3/18%') or ipc_class_symbol like ('G01M 3/22%') or ipc_class_symbol like ('G01M 3/28%')
intersect
SELECT a*
FROM tls201_appln a JOIN tls209_appln_ipc i
ON a.appln_id =i.appln_id /*join base and IPC table together*/
where ipr_type = 'PI'
and appln_kind != 'D'
and appln_filing_year < 9999
and ipc_class_symbol like ('B07C%') or ipc_class_symbol like ('B41J%') or ipc_class_symbol like ('B41K%') or ipc_class_symbol like ('G01B%') or ipc_class_symbol like ('G01C%') or ipc_class_symbol like ('G01D%') or ipc_class_symbol like ('G01F%') or ipc_class_symbol like ('G01G%') or ipc_class_symbol like ('G01H%') or ipc_class_symbol like ('G01J%') or ipc_class_symbol like ('G01K%') or ipc_class_symbol like ('G01L%') or ipc_class_symbol like ('G01M%') or ipc_class_symbol like ('G01N%') or ipc_class_symbol like ('G01P%') or ipc_class_symbol like ('G01R%') or ipc_class_symbol like ('G01S%') or ipc_class_symbol like ('G01V%') or ipc_class_symbol like ('G01W%') or ipc_class_symbol like ('G02F%') or ipc_class_symbol like ('G03G%') or ipc_class_symbol like ('G05B%') or ipc_class_symbol like ('G05F%') or ipc_class_symbol like ('G06%') or ipc_class_symbol like ('G07%') or ipc_class_symbol like ('G08C%') or ipc_class_symbol like ('G08G%') or ipc_class_symbol like ('G09B%') or ipc_class_symbol like ('G09C%') or ipc_class_symbol like ('G09G%') or ipc_class_symbol like ('G10L%') or ipc_class_symbol like ('G11B%') or ipc_class_symbol like ('G11C%') or ipc_class_symbol like ('H01L%') or ipc_class_symbol like ('H01P%') or ipc_class_symbol like ('H01Q%') or ipc_class_symbol like ('H03B%') or ipc_class_symbol like ('H03C%') or ipc_class_symbol like ('H03D%') or ipc_class_symbol like ('H03HF%') or ipc_class_symbol like ('H03G%') or ipc_class_symbol like ('H03H%') or ipc_class_symbol like ('H03J%') or ipc_class_symbol like ('H03K%') or ipc_class_symbol like ('H03L%') or ipc_class_symbol like ('H03M%') or ipc_class_symbol like ('H04B%') or ipc_class_symbol like ('H04H%') or ipc_class_symbol like ('H04J%') or ipc_class_symbol like ('H04K%') or ipc_class_symbol like ('H04L%') or ipc_class_symbol like ('H04M%') or ipc_class_symbol like ('H04N%') or ipc_class_symbol like ('H04Q%') or ipc_class_symbol like ('H04R%') or ipc_class_symbol like ('H04S%') or ipc_class_symbol like ('G02B 6%') or ipc_class_symbol like ('H01B 11%') or ipc_class_symbol like ('H01J 11%') or ipc_class_symbol like ('H01J 13%') or ipc_class_symbol like ('H01J 15%') or ipc_class_symbol like ('H01J 17%') or ipc_class_symbol like ('H01J 19%') or ipc_class_symbol like ('H01J 21%') or ipc_class_symbol like ('H01J 23%') or ipc_class_symbol like ('H01J 25%') or ipc_class_symbol like ('H01J 27%') or ipc_class_symbol like ('H01J 29%') or ipc_class_symbol like ('H01J 31%') or ipc_class_symbol like ('H01J 33%') or ipc_class_symbol like ('H01J 40%') or ipc_class_symbol like ('H01J 41%') or ipc_class_symbol like ('H01J 43%') or ipc_class_symbol like ('H01J 45%') or ipc_class_symbol like ('H01S 3%') or ipc_class_symbol like ('H01S 5%')

3. ICT-water sample PCP (1491 applications, to join to result above)
SELECT a*
FROM tls201_appln a JOIN tls209_appln_ipc i
ON a.appln_id =i.appln_id /*join base and IPC table together*/
where ipr_type = 'PI'
and appln_kind != 'D'
and appln_filing_year < 9999
and ipc_class_symbol like ('B07C%') or ipc_class_symbol like ('B41J%') or ipc_class_symbol like ('B41K%') or ipc_class_symbol like ('G01B%') or ipc_class_symbol like ('G01C%') or ipc_class_symbol like ('G01D%') or ipc_class_symbol like ('G01F%') or ipc_class_symbol like ('G01G%') or ipc_class_symbol like ('G01H%') or ipc_class_symbol like ('G01J%') or ipc_class_symbol like ('G01K%') or ipc_class_symbol like ('G01L%') or ipc_class_symbol like ('G01M%') or ipc_class_symbol like ('G01N%') or ipc_class_symbol like ('G01P%') or ipc_class_symbol like ('G01R%') or ipc_class_symbol like ('G01S%') or ipc_class_symbol like ('G01V%') or ipc_class_symbol like ('G01W%') or ipc_class_symbol like ('G02F%') or ipc_class_symbol like ('G03G%') or ipc_class_symbol like ('G05B%') or ipc_class_symbol like ('G05F%') or ipc_class_symbol like ('G06%') or ipc_class_symbol like ('G07%') or ipc_class_symbol like ('G08C%') or ipc_class_symbol like ('G08G%') or ipc_class_symbol like ('G09B%') or ipc_class_symbol like ('G09C%') or ipc_class_symbol like ('G09G%') or ipc_class_symbol like ('G10L%') or ipc_class_symbol like ('G11B%') or ipc_class_symbol like ('G11C%') or ipc_class_symbol like ('H01L%') or ipc_class_symbol like ('H01P%') or ipc_class_symbol like ('H01Q%') or ipc_class_symbol like ('H03B%') or ipc_class_symbol like ('H03C%') or ipc_class_symbol like ('H03D%') or ipc_class_symbol like ('H03HF%') or ipc_class_symbol like ('H03G%') or ipc_class_symbol like ('H03H%') or ipc_class_symbol like ('H03J%') or ipc_class_symbol like ('H03K%') or ipc_class_symbol like ('H03L%') or ipc_class_symbol like ('H03M%') or ipc_class_symbol like ('H04B%') or ipc_class_symbol like ('H04H%') or ipc_class_symbol like ('H04J%') or ipc_class_symbol like ('H04K%') or ipc_class_symbol like ('H04L%') or ipc_class_symbol like ('H04M%') or ipc_class_symbol like ('H04N%') or ipc_class_symbol like ('H04Q%') or ipc_class_symbol like ('H04R%') or ipc_class_symbol like ('H04S%') or ipc_class_symbol like ('G02B 6%') or ipc_class_symbol like ('H01B 11%') or ipc_class_symbol like ('H01J 11%') or ipc_class_symbol like ('H01J 13%') or ipc_class_symbol like ('H01J 15%') or ipc_class_symbol like ('H01J 17%') or ipc_class_symbol like ('H01J 19%') or ipc_class_symbol like ('H01J 21%') or ipc_class_symbol like ('H01J 23%') or ipc_class_symbol like ('H01J 25%') or ipc_class_symbol like ('H01J 27%') or ipc_class_symbol like ('H01J 29%') or ipc_class_symbol like ('H01J 31%') or ipc_class_symbol like ('H01J 33%') or ipc_class_symbol like ('H01J 40%') or ipc_class_symbol like ('H01J 41%') or ipc_class_symbol like ('H01J 43%') or ipc_class_symbol like ('H01J 45%') or ipc_class_symbol like ('H01S 3%') or ipc_class_symbol like ('H01S 5%')
intersect
SELECT a*
FROM tls201_appln a JOIN tls224_appln_cpc c
ON a.appln_id =c.appln_id /*join base and CPC table together*/
where ipr_type = 'PI'
and appln_kind != 'D'
and appln_filing_year < 9999
and cpc_class_symbol like ('Y02B 40/42%') or cpc_class_symbol like ('Y02B 40/44%') or cpc_class_symbol like ('Y02B 40/46%') or cpc_class_symbol like ('Y02B 40/48%') or cpc_class_symbol like ('Y02B 40/50%') or cpc_class_symbol like ('Y02B 40/52%') or cpc_class_symbol like ('Y02B 40/54%') or cpc_class_symbol like ('Y02B 40/56%') or cpc_class_symbol like ('Y02B 40/58%') or cpc_class_symbol like ('Y02B 30/12%') or cpc_class_symbol like ('Y02B 30/14%') or cpc_class_symbol like ('Y02B 30/16%') or cpc_class_symbol like ('Y02B 30/18%') or cpc_class_symbol like ('Y02B 30/80%') or cpc_class_symbol like ('Y02E 10/20%') or cpc_class_symbol like ('Y02E 10/28%') or cpc_class_symbol like ('Y02E 10/30%') or cpc_class_symbol like ('Y02E 10/32%') or cpc_class_symbol like ('Y02E 10/34%') or cpc_class_symbol like ('Y02E 10/36%') or cpc_class_symbol like ('Y02E 10/38%') or cpc_class_symbol like ('Y02E 60/364%') or cpc_class_symbol like ('Y02E 60/366%') or cpc_class_symbol like ('Y02B 30/545%') or cpc_class_symbol like ('Y02B 30/566%') or cpc_class_symbol like ('Y02B 30/762%')

I really appreciate your help in this.
Regards from Italy
Anna

[Geert Boedt]

Hello Anna,
"intersect" is a good way of merging conditions as well.
SELECT a.* must have a dot between the table alias and the * .
The "in" has the same function as a sequence of "ORs", but you have to be sure that the length of the IPC/CPC codes match with the attributes following the IN operator; you can not use % wildcards with "IN". That is why I use LEFT to truncate. You can combine a number of LEFTs with different lengths & different sets of IPC codes.
Keep in mind that the query must have 4 digits at subclass level, not more not less, so 'F03B7%' should be written as F03B 7%' you have to add 3 blanks before the 7. The same for 'F16K21%', it should be written as F16K 21%' --> 2 blanks before 21 (I think these blanks got in fact removed by the forum editor) . Also keep in mind the order of execution if you combine 'AND' and OR, you need to place brackets around your "OR" conditions if you want them executed as a group. You could probably join the result from the 3 queries with a UNION statement if your query stays within computational limits and is less then 10.000 characters long.

[annina]

Dear Geert,

thanks for your answer and I now added the brackets to the "or" statements - that helped already! With the spaces for the IPC codes, I had them all, but they might have been not copied or as you said, you removed them here.
I decided to stay with the intersect statement and the IPC codes as I have them now, otherwise I would need to rewrite them all. I might use the union statement to combine them depending on the computional limits (thanks for that hint too!).
So I am nearly ready to go, but I still have one last thing that doesn't work: It's when I want result from more then one table with the intersect statement.
Example:
SELECT a.*, i.*
FROM tls201_appln a JOIN tls209_appln_ipc i
ON a.appln_id =i.appln_id /*join base and IPC table together*/
where ipr_type = 'PI'
and appln_kind != 'D'
and appln_filing_year < 9999
and ipc_class_symbol like ('E03%') /*water*/
intersect
SELECT a.*, i.*
FROM tls201_appln a JOIN tls209_appln_ipc i
ON a.appln_id =i.appln_id /*join base and IPC table together*/
where ipr_type = 'PI'
and appln_kind != 'D'
and appln_filing_year < 9999
and (ipc_class_symbol like ('F16L 55/16%') or ipc_class_symbol like ('F17D 5%') or ipc_class_symbol like ('G01M 3/08%') or ipc_class_symbol like ('G01M 3/14%') or ipc_class_symbol like ('G01M 3/18%') or ipc_class_symbol like ('G01M 3/22%') or ipc_class_symbol like ('G01M 3/28%')) /*water 2*/

That brings 0 results. If I delete in both the "i.*" in the select statements, I get 2371 results. I tried to just do a.*, i.* with the first part (so before the intersect) and that works too (260,406 results).
Why can't I combine this (so have results from more than one table displayed with an intersect query)?
I would need this to have the other tables such as 206/207 added via outer joins and the data extracted.
Have a nice weekend
Anna

[Geert Boedt]

Hello Anna,
This is rather an SQL syntax question then a PATSTAT question.

The intersect looks for the same records in the 2 selects and returns (de-duplicated) rows only from the first SELECT statement that are identical to a row in the second SELECT statement. It does not return records where the appln_id only is matching.
The conditions on the ipc class make the records mutual exclusive so you get 0 results. If you remove the ipc attributes from the SELECT statement, then you will get all applications that fulfil the condition of having a combination of the IPC classes as specifed. A solution depends on what you need. You can simply accept that those applications effectively have the combination of the specified ipc codes, or you need to extract the tables with the (IPC) data if you need the classifications for further analysis. Another solution is to use the IN operator with 2 sub queries, one for each set of IPC conditions, and then simply join it with a third IPC table to get records that show the IPC. If you extract the tables (or install PATSTAT on a local server), then you could also create a list that contains all the IPC classification symbols concatenated in 1 attribute by means of a user defined function (not possible on PATSTAT Online).

Adding other tables such as TLS206 and TLS207 will work fine, as long as you don't specify WHERE conditions for those extra tables. You will then have multiple rows for 1 application, one for each person.

[annina]

Hi Geert,

yes I know - I also tried looking for help in coder forum but often they recommend doing things that I cant do with my access to Patstat online (like creating views etc), that's why I kind of rely a bit on you.
Anyway, after reading your last post I followed your advice and started downloading data I need excluding the IPC codes in the select statement, to at least have a start for my analysis.
Now checking the downloads, I saw that there is affectively a list 209_IPC (among all others such as 202, 212, 801) so do I understand correctly that the content of my computer display (table/result online) is not important for the download (PATSTAT subset)? I was assuming that the select statement had an influence of what I am able to download but it doesnt seem so. So also all outer joins of various tables such as 206/7 are not strictly necessary and it's enough to code for tls201 only (putting table subsets to worldwide on prepare download)?

Enjoy your weekend
Anna

[Geert Boedt]

Hello Anna,
you can download the "result table" as well as a "data extraction". The result table gives you an exact table according to the attributes you have specified in the SELECT part of the SQL query. An extraction gives you a set of tables according to a set of appln_id's generated by your query. It is irrelevant what tables you use to create that set and it is irrelevant what attributes you have in the SELECT, but the appln_id should be there because that defines the "primer set" to create the data base extraction.
So you could simply use something like

Code: Select all

SELECT appln_id from TLS209_APPLN_IPC WHERE ipc_class_symbol = 'E03D   9/00'

to extract a complete database that contains all the data for all patents that have been classified with "E03D 9/00". In the download screen you can then select any of the PATSTAT tables you would like to have (depending on you research), as well as whether you want the extraction to be extended to include family members or cited/citing applications. To illustrate it with your "water" example; the query below creates a list of appln_id's which does not give any useful information when looking at the "RESULT TABLE", but which is perfectly sufficient to extract a full data base extraction with any of the tables you need.

Code: Select all

SELECT distinct a.appln_id
FROM tls201_appln a JOIN tls209_appln_ipc i
ON a.appln_id =i.appln_id 
where ipr_type = 'PI'
and appln_kind != 'D'
and appln_filing_year < 9999
and (ipc_class_symbol like ('F16L  55/16%') or ipc_class_symbol like ('F17D   5%') 
or ipc_class_symbol like ('G01M   3/08%') or ipc_class_symbol like ('G01M   3/14%') 
or ipc_class_symbol like ('G01M   3/18%') or ipc_class_symbol like ('G01M   3/22%') 
or ipc_class_symbol like ('G01M   3/28%')) /* water 2 */
and a.appln_id in (select appln_id from tls209_appln_ipc where left(ipc_class_symbol,3) = 'E03' ) /* IPC water */

[annina]

Dear Geert,

thank you so much for that explanation. I guess I am good then.
Will make sure to name you on the preface page

Greetings from Milan
Anna