variables in SQL for monetdb

Question

I am confused whether at-sign variables could ever work in MonetDB. Is it standard SQL or only mySQL? (See e.g. this answer here on SO.) MonetDB claims to support SQL:2003 (full feature list here, hard for me to parse), but this is what they say on variables.

The following line fails in MonetDB complaining about the unexpected symbol :. But is there a way to get this work? I see no way to combine a SET (after DECLARE) with SELECT.

SELECT @firstq := QUANTILE(share26_2007,0.25) FROM sys.share26_2007;

(Afterwards, the following is the intended use case:)

SELECT peorglopnr, CASE WHEN share26_2007 < @firstq THEN 1

There are no variables in standard SQL. The SQL standard defines variables only for procedural languages (SQL/PSM). — , Jun 12 '14 at 16:16
@a_horse_with_no_name Thanks — then do you understand how/whether I can use `SET` with a `SELECT`? The monetdb example is about setting a declared variable with a value "manually," not with the result of a query… — László, Jun 12 '14 at 16:19
I have no experience with MonetDB. But if the manual says so, I would believe the manual. — , Jun 12 '14 at 16:22
@a_horse_with_no_name Any experience with `SET` would help, the documentation does not rule any of this out, just uses a simpler example. But of course, thanks already! — László, Jun 12 '14 at 16:23

cornuz · Answer 1 · 2016-09-30T10:56:24.257

As already pointed out in the comments, @ variables are not standard SQL.

Using DECLARE and SET would work::

DECLARE firstq double;
SET firstq = ( SELECT quantile(share26_2007, 0.25) FROM share26_2007 );

SELECT peorglopnr, CASE WHEN share26_2007 < firstq THEN 1 .....

Notes:

What I understand from your example is that you have a table share26_2007 which has a column share26_2007. I followed this assumption.
I declared the variable firstq as double. Your example does not specify the type of column share26_2007. Change the variable type accordingly.
When setting the value of a variable to the result of a SELECT that returns an atomic value, you do need parentheses around the SELECT.
At this moment quantile seems not to work correctly in MonetDB (see https://www.monetdb.org/bugzilla/show_bug.cgi?id=4076), but this is unrelated to your question. The syntax above works (you may want to verify that by replacing quantile with sys.quantile)

variables in SQL for monetdb

1 Answers1