I am doing a query with hql and i do not achieve that it works. it flaw join and coalesce. if I do the query without join, it me does not work coalesce.

@NamedQuery(name = "Stock.findByClientAndReference", query = "select distinct NEW com.fcclogistica.services.legacy.sga.entities.Stock(s.id, (select r.description from Reference r where r.id = s.id), s.situation, s.status, (select sum(ss.amount) - coalesce ((select sum(al.amount) from StockCapture al where al.id = s.id ), 0 ) - coalesce ((select sum(li.amount) from LineDeliveryNote li JOIN HeadDeliveryNote ca on ca.id = al.id and ca.deliveryNote = al.deliveryNote where li.id = s.id and ca.situation=''), 0 ) from Stock ss where ss.id = s.id and situation = '' group by ss.id)) from Stock s where s.id.client=? and s.id.code=? and situation = '' and status = 'B' order by s.id")})

which can be the problem?

Thank You

it is not possible to use select in coalesce????

Helps, please