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.

Answer 2

I solved it using "order by col_1_0_" in above query.. because hibernate creates column with names col_0_0_, col_1_0_, col_2_0_ and so on.. so if you just need to know the order of your column and add it to order by accordingly..

Thanks.

amar4kintu