QuerySyntaxException case in jpql

Question

I need to find entities by date, so I wrote this query annotation over method in JpaRepository:

@Query(
        "select distinct e " +
        "from SomeEntity e " +
        "join e.someChild c " +
        "where e.someField IN :types " +
        "and " +
            "case " +
                "when e.someDate is null then (:month = 12 and :day = 31) " +
                "else (month(e.someDate) = :month and day(e.someDate) = :day) " +
            "end"
)
List<SomeEntity> findSomeEntities(@Param("month") int monthValue, @Param("day") int dayOfMonth, @Param("types") Set<SomeSubclass> allowed);

Intellij is indicating compilation error between else and (: '(', <expression>, <operator>, END, FUNCTION or identifier expected, got '('. When compiling the following occurs:

Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected AST node: case near line 1, column 169

Edit:

This is the full anonymized query:

@Query(
        //@formatter:off
        "select distinct g " +
        "from SomeEntity g " +
        "join g.chileOne n " +
        "join g.childTwo l " +
        "join n.childThree ga " +
        "where g.someEntityTyp.typ IN :types " +
        "and " +
                "case " +
                    "when l.someDate is null then (:month = 12 and :day = 31) " +
                    "else (month(l.someDate) = :month and day(l.someDate) = :day) " +
                "end"
        //@formatter:on
)
List<SomeEntity> findSomeEntity(@Param("month") int monthValue, @Param("day") int dayOfMonth, @Param("types") Set<SomeEntityTyp.Typ> relevantSomeEntityTypes);

The error points to the beginning of case keyword.

Whereas this works fine when someDate is not null:

@Query(
        //@formatter:off
        "select distinct g " +
        "from SomeEntity g " +
        "join g.chileOne n " +
        "join g.childTwo l " +
        "join n.childThree ga " +
        "where g.someEntityTyp.typ IN :types " +
        "and month(l.someDate) = :month " +
        "and day(l.someDate) = :day")
        //@formatter:on
)
List<SomeEntity> findSomeEntity(@Param("month") int monthValue, @Param("day") int dayOfMonth, @Param("types") Set<SomeEntityTyp.Typ> relevantSomeEntityTypes);

There is no `else` in your query. also post the whole stacktrace — XtremeBaumer, Feb 15 '19 at 10:45
I'm sorry, you're right, I have posted the wrong version, now it's correct. The stack trace is long due to bean creation, it only points to the place, where intellij indicated error. — Andronicus, Feb 15 '19 at 10:53
And again you're right, but it's not the case, I have greatly rewritten the query to make it small and free of business logic. — Andronicus, Feb 15 '19 at 10:57
and there could be the issue. post the full query anonymized. and identify the substring around `column 169` so like 160 to 180 would be good to know — XtremeBaumer, Feb 15 '19 at 10:59
I believe [this answer](https://stackoverflow.com/a/8785288/7109162) is your issue — XtremeBaumer, Feb 15 '19 at 11:17

QuerySyntaxException case in jpql

0 Answers0