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

Question

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

I use: SELECT max(rank) FROM test

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

This is not a problem if I could "catch" zero with a simple if statement, but I cannot do this (the structure allows you to specify only a JPA request, but not java code).

Does anyone have an idea how to tune this query to get 0 instead of null if the table is empty? “The database is MySQL, and inline queries or stored procedures are not an option.”

Answer 1

May be:

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

EDIT

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