How to avoid null result of "SELECT max(rank) FROM test" for an empty table?

Question

I need to form an query in JPQL (2.0) to select a the max value (rank).

I use: SELECT max(rank) FROM test

This works fine, except the case where the table is empty, then the result is null, but I need 0.

This would be no problem if I was able to "catch" the null with an simple if-statement, but I can't do this (the framework allows only to specify an JPA Query but no java code).

Does anybody have an idea how to tune that query to get an 0 instead of an null if the table is empty? - The database is an MySQL, and native queries or stored procedures are no option.

Answer 1

Maybe:

SELECT COALESCE(MAX(rank), 0) FROM test

EDIT

COALESCE seems supported by JPQL (JPA 2.0): http://java.sun.com/developer/technicalArticles/JavaEE/JavaEE6Overview_Part3.html