problems with %-operator / retrieve green inventory IPCs

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

Thomas Oelker
Posts: 3
Joined: Fri Sep 20, 2019 10:50 am

problems with %-operator / retrieve green inventory IPCs

Post by Thomas Oelker » Fri Oct 11, 2019 9:21 am

Dear PATSTAT Community,

in broader terms, I want to get information about green patents as specified by the IPC green inventory (https://www.wipo.int/classifications/ip ... inventory/).

so I applied the following code:

Code: Select all

SELECT appln_filing_year, ipc_class_symbol, appln_auth, person_ctry_code
FROM tls201_appln 
JOIN tls209_appln_ipc  ON tls209_appln_ipc.appln_id = tls201_appln.appln_id
join tls207_pers_appln on tls207_pers_appln.appln_id = tls201_appln.appln_id
join tls206_person on tls206_person.person_id = tls207_pers_appln.person_id 
WHERE ipc_class_symbol LIKE 'H01M 4%' -- Change IPC symbol here
AND appln_filing_year BETWEEN 2000 AND 2000 -- Define year range here
GROUP BY appln_filing_year, ipc_class_symbol , appln_auth, person_ctry_code
ORDER BY appln_filing_year, ipc_class_symbol
This query yields 0 rows. But, when looking for

Code: Select all

...WHERE ipc_class_symbol LIKE 'H01M %'...
I can clearly find patents with the ipc code H01M 4/86 that is classified as green.

What might be the problem?

Thanks a lot in advance!
Best, Thomas


EPO / PATSTAT Support
Posts: 227
Joined: Thu Feb 22, 2007 5:33 pm
Contact:

Re: problems with %-operator / retrieve green inventory IPCs

Post by EPO / PATSTAT Support » Fri Oct 11, 2019 3:21 pm

Hello Thomas,
just a small syntax error: IPC and CPC codes where the maingroup level is used need to be written with 8 digits.
LIKE 'H01M 4%' is not the same as LIKE 'H01M4%' You need to replenish with leading blanks.
The "/" should always be on the 9 th position.

Code: Select all

SELECT appln_filing_year, ipc_class_symbol, appln_auth, person_ctry_code
FROM tls201_appln 
JOIN tls209_appln_ipc  ON tls209_appln_ipc.appln_id = tls201_appln.appln_id
join tls207_pers_appln on tls207_pers_appln.appln_id = tls201_appln.appln_id
join tls206_person on tls206_person.person_id = tls207_pers_appln.person_id 
WHERE ipc_class_symbol LIKE 'H01M   4%' -- or 'H01M   4/%'
AND appln_filing_year BETWEEN 2000 AND 2000 -- Define year range here
GROUP BY appln_filing_year, ipc_class_symbol , appln_auth, person_ctry_code
ORDER BY appln_filing_year, ipc_class_symbol
PATSTAT Support Team
EPO - Vienna
patstat @ epo.org


Thomas Oelker
Posts: 3
Joined: Fri Sep 20, 2019 10:50 am

Re: problems with %-operator / retrieve green inventory IPCs

Post by Thomas Oelker » Mon Oct 14, 2019 8:41 am

perfect, thank you very much!

Best, Thomas


Post Reply