17

I'm using H2 (with MySQL compatibility mode) to write some automated tests against our software that uses MySQL. Unfortunately, it seems like H2 does not have have the IF function that many of our queries use. Short of rewriting our application queries with something like DECODE, is their a good way to create the if function, say as an Alias?

The error that I'm getting:

WARNING: Failed to execute: SELECT IF(true,'TRUE!!','FALSE!!!') because: Function "IF" not found; SQL statement:
Vladimir Vagaytsev
  • 2,871
  • 9
  • 33
  • 36
vicsz
  • 9,552
  • 16
  • 69
  • 101

3 Answers3

10

I've just had the same issue, and I resolved it with CASE/WHEN/THEN SQL statement. So you can rewrite your query as follows:

SELECT CASE WHEN true THEN 'TRUE!!' ELSE 'FALSE!!!' END;

Surely it's more verbose, but it fits both H2 and MySQL.

Vladimir Vagaytsev
  • 2,871
  • 9
  • 33
  • 36
  • 1
    Has another advantage over MySql IF, it is almost standard implementation so it does decouple the query from the database used and thus makes for a better design overall – Arturo Mendes Aug 24 '23 at 11:29
7

Ended up just rewriting queries to use functions compatible with both database - H2, MySql. In my case, the functions in question were replaced with IFNULL.

vicsz
  • 9,552
  • 16
  • 69
  • 101
3

Yes you can create the if function as an alias:

CREATE ALIAS IF NOT EXISTS `IF` AS $$
    String ifFunction(boolean condition, String exp1, String exp2){
        if(condition) {
            return exp1;
        } else {
            return exp2;
        }
    }
$$;
Victor Basso
  • 5,556
  • 5
  • 42
  • 60
  • Thanks, this works for strings. I need an IF for strings and number, though. I've written a new question for this: http://stackoverflow.com/questions/37595970/create-alias-which-works-for-strings-and-numbers-in-h2 – Michael Koch Jun 02 '16 at 15:23