hibernate - HQL Order by query giving problem

Question

I have following query written in HQL for Hibernate.

========================================================================

select new map(ret.retailerDesc as ret_name, ret.id.retailerId as ret_id,
               ret.id.serviceId as service_id,  

(select count(distinct i.inspectionId) as inspections from Inspection i 
inner join i.clgCodeStatus c 
inner join c.retailerOrderses r  
inner join r.cusRetailer cr 
inner join i.inspectionMission m  where ret.id = cr.id  ) as inspections ,

(select count(distinct i.inspectionId) as inspections   from Inspection i 
inner join i.clgCodeStatus c 
inner join c.retailerOrderses r 
inner join r.cusRetailer cr 
inner join i.inspectionMission m 
where ret.id = cr.id  and i.inspectionResult = '1'  ) as match,

(select count(distinct i.inspectionId) as inspections   from Inspection i 
inner join i.clgCodeStatus c 
inner join c.retailerOrderses r 
inner join r.cusRetailer cr 
inner join i.inspectionMission m 
where ret.id = cr.id  and i.inspectionResult = '0'  ) as mismatch  )

from CusRetailer ret  order by inspections desc

=======================================================================

When above query executes it gives following error:

ERROR: column "inspections" does not exist

It is giving this error for "order by inspections desc". If I remove that it works fine.

Can anyone please help me to resolve this problem?

Thanks.

Answer 1

It might be a case that you need to repeat the expression for inspections

...from CusRetailer ret order by count(distinct i.inspectionId)

It may be that HQL doesn't support expressions in the order by clause, you may need to use a n SQL query instead.