HQL Case When error unexpected AST node: CASE near line 1

I have a query in my Repository.

@Query(Constant.QUERY)
List<Guide> findGuide(@Param("locationName") String locationName, @Param("gender") String gender, @Param("type") String type, @Param("language") String language);

and Constant.QUERY is

public static final String QUERY_FIND_GUIDE = "SELECT g.id FROM Guide g " + 
        " LEFT JOIN g.user u  " + 
        " LEFT JOIN g.locations l " + 
        " LEFT JOIN g.languages lang "+
        "WHERE (UPPER(:locationName)=UPPER('NULL') OR :locationName IS NULL OR :locationName ='' OR l.locationName = :locationName) "
        + " AND (UPPER(:gender)=UPPER('NULL') OR :gender IS NULL OR :gender ='' OR g.gender =:gender) "
        + " AND (:type IS NULL OR :type ='' OR "
        + " (CASE WHEN UPPER(:type)=UPPER('NULL') THEN (g.cardnumber IS NULL) ELSE (g.cardnumber IS NOT NULL) END) )"
        + " AND (UPPER(:language)=UPPER('NULL') OR :language IS NULL OR :language='' OR lang.language = :language)";

when i run the Page I have that error:

Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected AST node: CASE near line 1, column 376 [SELECT g.id FROM com.webapp.tgo.entities.Guide g  LEFT JOIN g.user u   LEFT JOIN g.locations l  LEFT JOIN g.languages lang WHERE (UPPER(:locationName)=UPPER('NULL') OR :locationName IS NULL OR :locationName ='' OR l.locationName = :locationName)  AND (UPPER(:gender)=UPPER('NULL') OR :gender IS NULL OR :gender ='' OR g.gender =:gender)  AND (:type IS NULL OR :type ='' OR  (CASE WHEN UPPER(:type)=UPPER('NULL') THEN (g.cardnumber IS NULL) ELSE (g.cardnumber IS NOT NULL) END) ) AND (UPPER(:language)=UPPER('NULL') OR :language IS NULL OR :language='' OR lang.language = :language)]

i think "IS NULL" can't use in result after "THEN", this true? if false pls help me check this error :(

Solution

I don't think HQL (or any other version of SQL) will let you use a CASE expression the way you are trying to use it. But, we can rephrase your logic as this:

WHERE
    (UPPER(:locationName) = UPPER('NULL') OR :locationName IS NULL OR
        :locationName = '' OR l.locationName = :locationName) AND
    (UPPER(:gender) = UPPER('NULL') OR :gender IS NULL OR :gender = '' OR
        g.gender = :gender) AND
    (:type IS NULL OR :type = '' OR
        ((UPPER(:type) = UPPER('NULL') AND g.cardnumber IS NULL) OR
        g.cardnumber IS NOT NULL) AND
    UPPER(:language) = UPPER('NULL') OR :language IS NULL OR :language = '' OR
    lang.language = :language)

The crux of my answer is that the followed CASE expression:

CASE WHEN UPPER(:type) = UPPER('NULL')
     THEN (g.cardnumber IS NULL)
     ELSE (g.cardnumber IS NOT NULL) END

gets rewritten to this:

(UPPER(:type) = UPPER('NULL') AND g.cardnumber IS NULL) OR g.cardnumber IS NOT NULL