0

How can I write following query in MonetDB:

CREATE TABLE "sampled" AS (
 SELECT *
 FROM   "base"
 WHERE  "target" = 'B'
 LIMIT  30
) UNION ALL (
 SELECT * 
 FROM "base" 
 WHERE "target" = 'A' 
 LIMIT 30 
) WITH DATA

?

When I attempt to execute the above query on:

MonetDB Database Server Toolkit v1.1 (Oct2014-SP2)

OS X 10.9.5

I am getting:

Error: syntax error, unexpected LIMIT, expecting INTERSECT or EXCEPT or UNION or ')' in: "create table "sampled" as (
SQLState:  42000
ErrorCode: 0 
Error:      select * 
SQLState:  22000 
ErrorCode: 0 
Error: from   "predictor_factory"."base"" 
SQLState:  22000 
ErrorCode: 0

Union alone works as expected:

SELECT *
FROM   "base"
WHERE  "target" = 'B'
UNION ALL
SELECT * 
FROM   "base"
WHERE  "target" = 'A'

Limit alone also works well:

SELECT * 
FROM   "base"
WHERE  "target" = 'A'
LIMIT  30

It's the combination that is troublesome for me.

Community
  • 1
  • 1
user824276
  • 617
  • 1
  • 7
  • 20

1 Answers1

1

MonetDB does not support LIMIT in subqueries. You could do the following

CREATE TABLE "sampled" AS SELECT * FROM "base" WHERE "target" = 'B' LIMIT 30 WITH DATA;
INSERT INTO "sampled" SELECT * FROM "base" WHERE "target" = 'A' LIMIT 30;
Hannes Mühleisen
  • 2,542
  • 11
  • 13
  • But I think that we can make it work if we wrap the `LIMIT` clause in a sql function that returns a table like. – dickoa Aug 27 '15 at 12:13