PatStat Online String Aggregate

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

Posts: 1
Joined: Thu Dec 10, 2015 9:07 pm

PatStat Online String Aggregate

Post by mbowen » Mon Dec 14, 2015 10:12 pm

Hi, I'm trying to get PatStat Online to do a string aggregate concatenation in a few places and have run into problems. From the research I've done, there appear to be only a few ways to do this.
  1. Built-in function, which MSSQL, and therefore PatStat Online, doesn't have.
  2. Stored procedure, which I expect cannot be created on PatStat Online.
  3. WITH based recursion, not supported or too computationally expensive (not sure which).
  4. STUFF/FOR XML sub-query. Which also seems to be restricted.
Of the non-built-in options, I like #4 the best. It's clean and easy to use, but PatStat Online complains about the FOR XML PATH('') part, saying something about it being prohibited. Is there some reason for this restriction? I'd really like to be able to use it something like this:

Code: Select all

		SELECT '|' + person_name 
			FROM tls206_person pe 
			JOIN tls207_pers_appln pa ON pe.person_id = pa.person_id
			WHERE pa.appln_id = 51651 -- For purposes of this example
			FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
) AS person_name
On the other hand, if anyone knows of another method to do a string aggregate that will work in PatStat Online, I'd appreciate it if you'd post how to do it here.

Matt Bowen

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

Re: PatStat Online String Aggregate

Post by Geert Boedt » Mon Jan 18, 2016 8:39 am

Dear Matt,

Thanks for your interest in PATSTAT. You are really doing quite some interesting stuff with it. You raised 2 issues:

1) PATSTAT Online does not support the WITH statement. We only support SELECTs; every query should start with SELECT ... .

2) The STUFF / FOR XML PLAN construct should be supported, but obviously it does not work. We forwarded this problem to our developers. We assume there is an issue with the public domain JDBC driver. We will raise a bug report for the JDBC driver, but we have no control when or whether this will be fixed. Anyway, thanks for pointing this out.

For doing advanced data aggregation exercises, one should consider subscribing to PATSTAT Raw Data. PATSTAT raw data can be loaded into your own database platform, and you do not have the limitations of PATSTAT Online (read-only, user defined functions, views, temporary tables, etc...)

As a possible work around you could also download the data as a MS ACCESS database (or as CSV files), and then take it from there.

We hope this helps.
Best regards,

Geert Boedt
PATSTAT support
Business Use of Patent Information
EPO Vienna

Post Reply