Is there a function in MonetDB that is equivalent to IF?

Question

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

I have never worked with MonetDB but standard SQL syntax for that would be `SELECT CASE WHEN Value>0 THEN Value ELSE -1 END AS SelectedValue FROM table` which should be supported according to MonetDB documentation — Nenad Zivkovic, Jul 19 '13 at 14:54
@NenadZivkovic In that case is possible to do concatenations? For example: `case when value> 0 then value else -1 end || case when value < 0 then 5 else 0 end` (assume everything's varchars for concatenations) — Marina, Jul 19 '13 at 15:25
While I guess it's possible, I have no idea what this example is suppose to do :) Can you update your question with example and expected result? — Nenad Zivkovic, Jul 19 '13 at 15:52
It is possible, I tested it. However, my question was meant to avoid using case statements. The 'If' function is really what I was looking for. — Marina, Jul 19 '13 at 15:56
are you actually looking for the sql `WHERE` clause? the example you've provided above can be easily solved with case statements like.. `CASE WHEN value > 0 THEN value WHEN value < 0 THEN 5 ELSE -1 END` — Anthony Damico, Jul 19 '13 at 16:22

score 6 · Accepted Answer · edited May 23 '17 at 11:46

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:

F261: CASE expression
F261-01: Simple CASE
F261-02: Searched CASE

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

Is there a function in MonetDB that is equivalent to IF?

1 Answers1