PatStat Online String Aggregate

[mbowen]

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
(
	STUFF((
		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)')
		,1,1,'')
) 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.

Thanks,
Matt Bowen

[Geert Boedt]

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.