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.
- Posts: 1
- Joined: Thu Dec 10, 2015 9:07 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.
- Built-in function, which MSSQL, and therefore PatStat Online, doesn't have.
- Stored procedure, which I expect cannot be created on PatStat Online.
- WITH based recursion, not supported or too computationally expensive (not sure which).
- 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.
- Posts: 178
- Joined: Tue Oct 19, 2004 10:36 am
- Location: Vienna
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.
Business Use of Patent Information