Problem with GROUP_CONCAT()

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

sydu
Posts: 3
Joined: Thu Jan 14, 2016 8:37 am

Problem with GROUP_CONCAT()

Post by sydu » Thu Jan 14, 2016 8:52 am

Hello all,

I have a problem with the new version of PATSTAT (5.05), before I worked with the 5.01.01 version.
With this previous version, I could use the GROUP_CONCAT() instruction of MySQL but with this new version, I could not.

To give an example, I used this instruction like this:

SELECT DISTINCT t201.appln_nr, t201.earliest_filing_year, t201.appln_auth, t206.person_ctry_code, t206.han_harmonized, t206.hrm_L2,
GROUP_CONCAT(DISTINCT t209.ipc_class_symbol) AS list_ipc_class_symbol
FROM tls201_appln t201

LEFT OUTER JOIN tls209_appln_ipc t209 ON t201.appln_id = t209.appln_id
LEFT OUTER JOIN tls207_pers_appln t207 ON t201.appln_id = t207.appln_id
LEFT OUTER JOIN tls206_person t206 ON t207.person_id = t206.person_id
WHERE t209.ipc_class_symbol LIKE 'XXXX'
AND t201.earliest_filing_year >= XXXX

The goal of this instruction is to have all IPC on the same row in the result table (A44C 15/00,A45C 11/18,A45F 5/00,…)
But with the 5.05 version, I have an error message: 'GROUP_CONCAT' is not a recognized aggregate function.

So, I would know if there is a mistake in my code or if there is a mean to have the same result with the new version of PATSTAT?

Thank you in advance.
Kind regards.

Sydu


Geert Boedt
Posts: 178
Joined: Tue Oct 19, 2004 10:36 am
Location: Vienna

Re: Problem with GROUP_CONCAT()

Post by Geert Boedt » Thu Jan 14, 2016 4:03 pm

Hello Sydu,
the group_concat instruction is proprietary to MySQL and is not available in MS SQL.
We changed platform last year, and therefore that function can not be used any more.
Best regards,

Geert Boedt
PATSTAT support
Business Use of Patent Information
EPO Vienna


sydu
Posts: 3
Joined: Thu Jan 14, 2016 8:37 am

Re: Problem with GROUP_CONCAT()

Post by sydu » Thu Jan 21, 2016 10:59 am

Thank Geert for this answer.
After many searches I had found this difference between MySQL and SQL MS but i forgot to say it here.
Do you know if it's possible to have the same result with SQL MS? (All my attempts have failed)


Geert Boedt
Posts: 178
Joined: Tue Oct 19, 2004 10:36 am
Location: Vienna

Re: Problem with GROUP_CONCAT()

Post by Geert Boedt » Thu Jan 21, 2016 4:23 pm

As far as I know, not with PATSTAT Online. The sql query in PATSTAT Online goes first through a parser that "translates" it to SQL.
Maybe you should consider purchasing PATSTAT raw data and install it on a local server. There are user-made functions available on MS SQL that simulate the group-concat function from MySQL.
Best regards,

Geert Boedt
PATSTAT support
Business Use of Patent Information
EPO Vienna


sydu
Posts: 3
Joined: Thu Jan 14, 2016 8:37 am

Re: Problem with GROUP_CONCAT()

Post by sydu » Fri Jan 22, 2016 2:17 pm

Once again, thank you Geert.
I will look more closely at the benefits of the paid version.


Post Reply