JPA - Syntax error parsing SELECT GREATEST(c.field, mc.field), LEAST(c.field, mc.field) FROM table

Question

I need to get the greatest and least value between two fields. I'm using JPA 2.1 with EclipsLink.

Here's my query simplified:

SELECT GREATEST(c.min, mc.max), LEAST(c.max, mc.max) 
FROM
   MethodCategory mc, OperationMethod om, Client c 
       JOIN
   User u ON c.id = u.client.id
WHERE
   om.method = 1
   AND om.method.category = "SUPER"
   AND om.isDeleted = false 
   AND om.user = u
   AND u.id <> 1 
   AND c.isOnline = TRUE 
   AND c.isActive = TRUE;

It does work fine on MySQL Workbench, as a raw sql query but when I replicate this query to my entityManager.createQuery(query) I get an exception.

Here's how I replicate the query to JPA:

TypedQuery<Object[]> query = em.createQuery("SELECT GREATEST(c.min, mc.min), LEAST(c.max, mc.max) "
                + " FROM\n"
                + "    MethodCategory mc, OperationMethod om, Client c \n"
                + "        JOIN\n"
                + "    User u ON c.id = u.client.id \n"
                + " WHERE\n"
                + "    om.method = :method \n"
                + "    AND om.method.category = :categoryName \n"
                + "    AND om.isDeleted = false \n"
                + "    AND om.user = u \n"
                + "    AND u.id <> :userId \n" 
                + "    AND c.isOnline = TRUE \n"
                + "    AND c.isActive = TRUE \n",
                Object[].class);

Whenever I execute this JPQL query I get this exception:

Exception Description: Syntax error parsing [SELECT GREATEST(c.min, mc.min), LEAST(c.max, mc.max)  FROM (...)
[14, 15] The SELECT clause has 'GREATEST' and '(c.min, mc.min)' that are not separated by a comma.
[72, 73] The SELECT clause has 'LEAST' and '(c.max, mc.max)' that are not separated by a comma.

It really makes no sense to me this exception. I'm clearly separating the GREATEST() and LEAST() with a comma and GREATEST , (value) does not make any sense.

What am I doing wrong here?

Answer 1

I ended up with the following solution, a mix of MIN/MAX with CASE THEN

Here's the query:

SELECT  
MIN(CASE WHEN (c.min < mc.min) THEN mc.min ELSE c.min END) as min, 
MAX(CASE WHEN (c.max < mc.max) THEN c.max ELSE mc.max END) as max  
FROM ...