I need to do something like this in MonetDB:
select
If(value > 0, value, -1) as selected_value
from
table
In mysql, this means 'return value if value > 0, otherwise return -1.' Is there an equivalent function to this in MonetDB?
And I am not looking for a case statement. I know it exists in both MonetDB and other sql languages. I would like specifically to know of something like the example I gave.
Thank you
As far as I know there isn't an equivalent function to this in MonetDB.
The if() function is part of the MySQL Extensions to Standard SQL, but this extension in not part of the monet builtin function
Like many other SQL database also in monetdb is not available an ´IF()´ function because isn't part of the standard SQL language.
For other evidence see this question and try your query in a standard parser.
I'm am aware that you are not looking for a case statement, howsoever , according to SQL in a Nutshell: ANSI SQL2003 provides a function named CASE] that can be used to create IF-THEN flow-control logic within queries or update statements.
The CASE function provides IF-THEN-ELSE functionality within a SELECT or UPDATE statement. It evaluates a list of conditions and returns one value out of several possible values.
CASE has two usages: simple and searched. Simple CASE expressions compares one value, the input_value, with a list of other values and return a result associated with the first matching value. Searched CASE expressions allow the analysis of several logical conditions and return a result associated with the first one that is true.
monetdb supported features includes:
Therefore , in my opinion, the best chance is to code a ´Boolean searched operation´ is:
SELECT
CASE WHEN value > 0 THEN value ELSE -1 END AS selected_value
FROM
table;
Furthermore The monetdb dcumentation about flowcontrol reports that it allows both ´CASE WHEN´ and ´IF THEN ELSE´and the SQL standard allows to create SQL functions and MonetDB has this support.
I think ,but I don't encourage and not tested, that you might define your own function; something like that:
CREATE FUNCTION MY_CONV(value int)
RETURNS int
BEGIN
IF (value > 0)
THEN RETURN value
ELSE RETURN -1;
END IF;
END;
hence
SELECT MY_CONV(value) FROM table;
Even more complicated could be try to code a UserDefinedFunction