DB2 syntax when running H2 (in DB2 mode)

Question

I have the following query which works nice when running directly against DB2:

String sql =
"select slutt_dato + 1 day  as tDato from klv80201 " +
"union " +
"select fra_dato as tDato from klv12101 where avtalenr = :avtalenr and kundenr = :kundenr " +
"union " +
"select fra_dato as tDato from klv12401 where avtalenr = :avtalenr and kundenr = :kundenr and MEDLEMSTATU < '32' " +
"order by tDato desc fetch first 1 rows only;";

But when I run it through a test with H2 as database with the following configuration:

jdbc:h2:mem:play;MODE=DB2;LOCK_TIMEOUT=10000;LOCK_MODE=0

I get the following error message:

org.h2.jdbc.JdbcSQLException: Column "SLUTT_DATO" not found; SQL statement: select slutt_dato + 1 day as tDato from klv80201 union select fra_dato as tDato from klv12101 where avtalenr = ? and kundenr = ? union select fra_dato as tDato from klv12401 where avtalenr = ? and kundenr = ? and MEDLEMSTATU < '32' order by tDato desc fetch first 1 rows only; [42122-149]

If I remove the "+1 day" the query works nice in H2. The error message from the H2 JDBC driver is wrong, column "SLUTT_DATO" does exist and works nice when removing "+1 day".

Any good suggestions why H2 acts like it does? Is it a bug or is it me that misunderstands something?

Thanks in advance

Answer 1

H2 doesn't understand + 1 day and gets confused here. This will work however:

"select slutt_dato + 1 as tDato from klv80201 "

But I'm afraid this will no work for IBM DB2... I don't think there is an easy solution to this problem that works on both IBM DB2 and H2.