Something is wrong with my query... help needed :(

[Elisa_D]

Dear Community,
I am trying to set a query in order to retrive data about applications for patents in the EPO by US applicants in different sectors form 1990 to 2020.

In first place I looked at the amount of applications form USA applicants in EPO in all sectors with this query:

Code: Select all

SELECT DISTINCT a.*
FROM tls201_appln a
JOIN tls207_pers_appln pa on a.appln_id = pa.appln_id
JOIN tls206_person p on pa.person_id = p.person_id
WHERE appln_auth = 'EP'
AND appln_kind = 'A'  -- exclude PCT filings where the EPO only served as the Receiving Office, utility models and design models
AND a.appln_id < 900000000   -- exclude artificial applications 
AND appln_filing_year >=1990
AND applt_seq_nr > 0  -- consider only applicants
AND person_ctry_code = 'US'  -- applicant must be from United States

I am quite confident it is correct in order to achieve my goal, but a confirmation from you will be deeply appreciated.

Then I need to look at the applications from US applicants in EPO, from 1990 to 2020, in the IPC class A23D (edible oils or fats) and A23J (proteins compositions for foodstuff), but I also need to exclude A23J 3 (Working-up of proteins for foodstuff). This is the query I used, but looking at the result table there' something not working:

Code: Select all

SELECT DISTINCT a.*, ipc_class_symbol
FROM tls201_appln a
JOIN tls207_pers_appln pa on a.appln_id = pa.appln_id
JOIN tls206_person p on pa.person_id = p.person_id
JOIN tls209_appln_ipc i on a.appln_id = i.appln_id
WHERE appln_auth = 'EP'
AND appln_kind = 'A'  -- exclude PCT filings where the EPO only served as the Receiving Office, utility models and design models
AND a.appln_id < 900000000   -- exclude artificial applications 
AND appln_filing_year >=1990
AND applt_seq_nr > 0  -- consider only applicants
AND person_ctry_code = 'US'  -- applicant must be from United States
AND ipc_class_symbol LIKE 'A23D'
OR ipc_class_symbol LIKE 'A23J'
AND NOT ipc_class_symbol LIKE 'A23J   03'

With this query, in the result table, I obtain 878 documents from other appln_auth than EP and with filing year prior to 1990.
Could you please give me some advice?
Thank you so much
Elisa

[EPO / PATSTAT Support]

Hello Elisa,
your first query is perfectly ok. You could have omitted the "AND appln_kind = 'A' -- exclude PCT filings where the EPO only served as the Receiving Office, utility models and design models" , but it does not harm.
In older PATSTAT releases, this condition was necessary because PCT applications filed at any PCT receiving office had indeed the same appln_auth code combined with a kind code "W". But this was changed a couple of years back so that all PCT applications will have appln_auth = 'WO' in combination with the kind code "W".
EPO does not have utility or design models registration. But for other patent offices, filtering them out might be relevant. I would however recommend to do this via ipr_type attribute in the tls201_appln table.
The second query is indeed wrong because of the execution order of the conditions in the WHERE clause, and also because you have to use a % wild card if you want the LIKE instruction to do what you want it to do.
Below is a query how I would do it, but there are different methods to make a correct query that would fulfill your needs. It depends a bit on what data you want in your final table, and how that data should look.

Code: Select all

SELECT a.appln_id, appln_auth, appln_nr, appln_kind, appln_filing_date, appln_filing_year, appln_nr_epodoc 
,appln_nr_original, ipr_type, receiving_office, internat_appln_id, int_phase, reg_phase, nat_phase, earliest_filing_date 
,earliest_filing_year, earliest_filing_id, earliest_publn_date, earliest_publn_year, earliest_pat_publn_id, granted 
,docdb_family_id, inpadoc_family_id, docdb_family_size, nb_citing_docdb_fam, nb_applicants, nb_inventors
, STRING_AGG (cast((ipc_class_symbol)as NVARCHAR(MAX)), ' ¦ ')  IPC_group --grouping all the IPC symbols in 1 cell
FROM tls201_appln a
JOIN tls207_pers_appln pa on a.appln_id = pa.appln_id
JOIN tls206_person p on pa.person_id = p.person_id
JOIN tls209_appln_ipc i on a.appln_id = i.appln_id
WHERE appln_auth = 'EP'
AND a.appln_id < 900000000   -- exclude artificial applications 
AND appln_filing_year >=1990
AND applt_seq_nr > 0  -- consider only applicants
AND person_ctry_code = 'US'  -- applicant must be from United States
AND a.appln_id in (select appln_id from tls209_appln_ipc where left(ipc_class_symbol,4) in ('A23D',  'A23J') and left(ipc_class_symbol,8) <> 'A23J   3')
group by a.appln_id,appln_auth, appln_nr, appln_kind, appln_filing_date, appln_filing_year, appln_nr_epodoc 
,appln_nr_original, ipr_type, receiving_office, internat_appln_id, int_phase, reg_phase, nat_phase, earliest_filing_date 
,earliest_filing_year, earliest_filing_id, earliest_publn_date, earliest_publn_year, earliest_pat_publn_id, granted 
,docdb_family_id, inpadoc_family_id, docdb_family_size, nb_citing_docdb_fam, nb_applicants, nb_inventors
order by a.appln_id

Geert BOEDT

[Elisa_D]

Thank you so much for your help!!