Named query in spring repository gives error on mysql DATE function - (', <expression>, FUNCTION or identifier expected, got '('

I have the following query in a spring boot repository:

    @Query("SELECT " +
            "    new com.vw.asa.Response.Dashboard.Charts.OverallStatus(gm.status, COUNT(gm)) " +
            "FROM " +
            "    GenMeasures gm " +
            "WHERE gm.status IN ('OPEN', 'RESOLVED', 'CLOSED', 'IN_PROGRESS', 'RISK_ACCEPTED') AND gm.dueDate < DATE(NOW()) OR gm.dueDate IS NULL " +
            "GROUP BY " +
            "    gm.status")
    List<OverallStatus> getOverallStatus();

Running this query in the equivalent manner in MySQL works fine, however - when writing this query here, it returns the error: <expression>, <operator>, GROUP, HAVING or ORDER expected, got '(' at the ( after the DATE function.

This error also occurred when I placed the ( after the WHERE in an effort to group parts of the clause. What is causing this error?

EDIT

Removing the gm.dueDate < DATE(NOW()) OR piece of the clause seems to remove the error. Why does it have a problem with the DATE() function?

1 answer

  • answered 2021-02-27 04:32 Typ0

    Try this:

    "WHERE gm.status IN ('OPEN', 'RESOLVED', 'CLOSED', 'IN_PROGRESS', 'RISK_ACCEPTED') AND (gm.dueDate < DATE(NOW()) OR gm.dueDate IS NULL)