COALESCE with HQL

Hibernate ORM
2

No it’s not possible. Think about it, what should be the column that determines one path is null but the other isn’t? What should the SQL be? One possible interpretation could be:

select
  coalesce(t1.col1, t2.col1),
  coalesce(t1.col2, t2.col2),
...

Another possible interpretation could be:

select
  case when t1.id is null then t2.col1 else t1.col1 end,
  case when t1.id is null then t2.col2 else t1.col2 end,
...

I don’t know if everyone is happy with one or the other solution, but on top of all that, there are further internal challenges with allowing this kind of thing, because functions currently can’t return entities etc.

I’d suggest you to write out the case when statements yourself and construct a DTO from the result.

1 Like