HQL QuerySyntaxException: unexpected AST node-CodePudding

Searched and found a lot of questions about this but nothing for my particular case. I am getting an error on my HQL query, here is the code:

final Query query = session.createQuery(
                    " SELECT DISTINCT e "  
                            " FROM Employee e"  
                            " INNER JOIN Requisition r on r.supervisor = e.id "  
                            " WHERE r.status = 'Open' "  
                            " AND r.isEvergreen = false "  
                            " AND r.isConfidential = false "  
                            " AND r.employmentType != 'Intern (Fixed Term)' "  
                            " AND ("  
                            "   CASE WHEN :searchString IS NOT NULL THEN (CONCAT(e.firstName, ' ', e.lastName) LIKE CONCAT('%', TRIM(:searchString), '%')) END)"  
                            " ORDER BY e.firstName, e.lastName")
                    .setParameter("searchString", searchString);

And here's the error log:

org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected AST node: CASE near line 1, column 329 [ SELECT DISTINCT e  FROM <Insert the rest of the query>

The query works if I remove the CASE WHEN statement in the final AND and the setParameter. So that means something is wrong with the final AND (...) and/or the newly introduced parameter.

I am new to Hibernate and am struggling with this since the error message isn't super helpful. Any ideas?

CodePudding user response：

The case you have does not complete the condition. You can simplify the case as follows :

(coalesce(:searchString,1,0) =1 OR e.firstName||' '|| e.lastName LIKE '%'||:searchString||'%')

The first coalesce will check if searchstring is null , if it is will return 1 and 0 if it is not.
If it returns 1 , the expression after OR will not get evaluated.
If first expression return 0 , the expression after OR will be evaluated

This will fulfill your usecase to apply searchString filter only when it is not null.