COALESCE in JPA namedQuery

Question

I have the following namedQuery

select new test.entity.Emp(COALESCE(k.projectId,'N')
as projectId, k.projectName) from Emp o inner join o.projects k 

However I am getting error

expecting RIGHT_ROUND_BRACKET, found '('

How to handle COALESCE in namedQuery?

Are there any other ways to handle null values in JPA?

Answer 1

Coalesce is supported by JPA 2.0 API.

The new construct is proprietary to Hibernate, not necessarily supported in all JPA implementations. First try the query without also trying to construct an object:

select COALESCE(k.projectId,'N') as projectId, k.projectName from Emp o inner join o.projects k