Help with query on Brazil

[fabian_scheifele]

Hello everyone,

I am trying to retrieve the total number of patents that were filed with the Brazilian Patent Authority(appln_auth = 'BR') for IPC classifications E21B% and F03D%, sorted by the nationality of applicant (person_ctry_code) and the year of filing (appln_filing_year)
I created the following query and i got result=0, even though I know from the Brazilian institute INPI that there are filings by brazilian national (Furthermore I checked with ctry_code= US and DE and also got zero). I think i am doing something wrong on the joint command, but not sure. Help would be highly appreciated!

SELECT DISTINCT a.*, person_ctry_code appln_filing_year
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 b on b.appln_id = pa.appln_id
WHERE appln_auth = 'BR'
AND appln_kind = 'A' -- exclude PCT filings where the EPO only served as the Receiving Office
AND a.appln_id < 900000000 -- exclude artificial applications (see PATSTAT Data Catalog for details)
AND applt_seq_nr > 0 -- consider only applicants
AND ipc_class_symbol='E21B%'
AND person_ctry_code = 'BR'

[Geert Boedt]

Hello Fabian,
just a small syntax error: the % wildcard should be used with the LIKE operator. Or you could use LEFT function.

Code: Select all

SELECT DISTINCT a.*, person_ctry_code appln_filing_year
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 b on b.appln_id = pa.appln_id
WHERE appln_auth = 'BR'
AND appln_kind = 'A' -- exclude PCT filings where the EPO only served as the Receiving Office
AND a.appln_id < 900000000 -- exclude artificial applications (see PATSTAT Data Catalog for details)
AND applt_seq_nr > 0 -- consider only applicants
AND ipc_class_symbol like'E21B%' -- or you could use left(ipc_class_symbol,4)='E21B'
AND person_ctry_code = 'BR'