PROBLEMS WITH "CREATE VIEW" IN PATSTAT ONLINE

[Ricardo]

Dear all,

I am running the following SQL on PATSTAT ONLINE Apr.2014 edition from an example given by Rassenfosse et al, 2014.
SELECT
DISTINCT
t1.appln_id, t1.appln_auth, t1.appln_nr, t1.appln_kind
FROM tls201_appln t1
INNER JOIN
tls209_appln_ipc t2 ON t1.appln_id = t2.appln_id
WHERE
year(t1.appln_filing_date) = 2005
AND (t1.appln_kind = 'A' OR t1.appln_kind = 'W')
AND t2.ipc_class_symbol LIKE 'F03D%'

ORDER BY t1.appln_auth, t1.appln_id;

CREATE VIEW our_sample AS

[DISTINCT
t1.appln_id, t1.appln_auth, t1.appln_nr, t1.appln_kind
FROM tls201_appln t1
INNER JOIN
tls209_appln_ipc t2 ON t1.appln_id = t2.appln_id
WHERE
year(t1.appln_filing_date) = 2005
AND (t1.appln_kind = 'A' OR t1.appln_kind = 'W')
AND t2.ipc_class_symbol LIKE 'F03D%'

ORDER BY t1.appln_auth, t1.appln_id;
CREATE VIEW our_sample AS];
15:02:14 [SELECT - 0 row(s), 0 secs] [Error Code: 1064, SQL State: 42000] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE VIEW our_sample AS [DISTINCT t1.appln_id, t1.appln_auth, t1.appln_nr' at line 14

What am I doing wrong?
I'd be really grateful for any advice, Thanks Ricardo


*An introduction to the Patstat database with example queries, Rassenfosse et al, 2014

[nico.rasters]

It should be:

CREATE VIEW our_sample AS
SELECT DISTINCT
t1.appln_id, t1.appln_auth, t1.appln_nr, t1.appln_kind
FROM tls201_appln t1
INNER JOIN tls209_appln_ipc t2 ON t1.appln_id = t2.appln_id
WHERE year(t1.appln_filing_date) = 2005
AND (t1.appln_kind = 'A' OR t1.appln_kind = 'W')
AND t2.ipc_class_symbol LIKE 'F03D%';

You forgot "SELECT" and the [ should not be there.

See also http://dev.mysql.com/doc/refman/5.0/en/create-view.html

What are you going to do with these wind energy patents from 2005?

[mkracker]

Hi Ricardo,

What Nico wrote is correct, if you have created your own database by loading PATSTAT raw data.

However, you wrote that you are using PATSTAT Online, which is the hosted version of PATSTAT. PATSTAT Online has some additional features, but also some limitations. Most notably, it is a read-only database. Therefore you cannot define and store your own data or tables. Therefore you cannot create a SQL view, which is in fact a virtual table.
In short: PATSTAT Online allows only SELECT statements.

Depending on what you need to do, you may choose one of these options:

• You may just extend / modify the example query given by Rassenfosse till it fits your needs.

• You may execute the SELECT statement and download the result list and process it locally, e. g. with Excel. This download feature is available in the Table Window.

• Or you may execute the SELECT statement and download one or more tables which are related to your result set. You then may further process the then much smaller downloaded database on your local computer, e. g. with MS Access.
  You just have to make sure that your query includes an APPLN_ID attribute, which is required as a seed for finding all other information you may request in the download. This “subset download” feature is available in the Result Window.

Martin Kracker / EPO