Hibernate Community • View topic - HQL - Arithmetic Order By alternative?

View unanswered posts | View active topics

Board index » Hibernate & Java Persistence » NHibernate Users

All times are UTC - 5 hours [ DST ]

HQL - Arithmetic Order By alternative?

Page 1 of 1

[ 3 posts ]

Previous topic | Next topic

Author

Message

seth

Post subject: HQL - Arithmetic Order By alternative?

Posted: Wed Apr 19, 2006 4:16 pm

Newbie

Joined: Wed Apr 19, 2006 10:14 am
Posts: 2

Hibernate version: 1.0.2.0
Name and version of the database you are using: Oracle

Currently, I have a query that works in SQL, but I would like to make it work with HQL. I have two related tables:

Store : Store_Id, Zipcode_Id
Zipcode: Zipcode_id, Zipcode, Lat, Long

i'm using an earth distance calculation to determine what stores are nearby based on a users entered zipcode.

Link to calculation:
http://www.meridianworlddata.com/Distance-Calculation.asp

In the following query, :zipLat and :zipLng are looked up against the zipcode table based upon the entered zipcode.

Code:

      select 
         {ci.*} 
         from Store ci inner join Zipcode zc ON 
         ci.Zipcode_id = zc.Zipcode_id 
         WHERE sqrt( power( 69.1 * (zc.Lat - :zipLat), 2 ) + 
         power( 69.1 * (zc.Lng - :zipLng) * cos(:zipLat/57.3), 2) ) <= :maxDistance 
         order by sqrt( power( 69.1 * (zc.Lat - :zipLat), 2 ) + 
         power( 69.1 * (zc.Lng - :zipLng) * cos(:zipLat/57.3), 2)  ) 

Distance is calculated, but I need to sort/order by it. I tried the following HQL, but I think I'm barking up the wrong tree. Order by cannot contain Arithmetic operators. Any suggestions? :

Code:

         select ci 
         from Store as ci, Zipcode as zc 
         where ci.Zipcode = zc and 
         sqrt( power( 69.1 * (zc.Lat - :zipLat), 2 ) + 
         power( 69.1 * (zc.Lng - :zipLng) * cos(:zipLat/57.3), 2) ) <= :maxDistance 
         group by ci 
         order by max( sqrt( power( 69.1 * (zc.Lat - :zipLat), 2 ) + 
         power( 69.1 * (zc.Lng - :zipLng) * cos(:zipLat/57.3), 2)  ) )

Thanks much!

Top

seth

Post subject: Re: HQL - Arithmetic Order By alternative?

Posted: Tue Apr 25, 2006 10:21 am

Newbie

Joined: Wed Apr 19, 2006 10:14 am
Posts: 2

I would prefer to use HQL. Can I sort a collection based on a a calculated field? See the problem below.

seth wrote:

Code:

      select 
         {ci.*} 
         from Store ci inner join Zipcode zc ON 
         ci.Zipcode_id = zc.Zipcode_id 
         WHERE sqrt( power( 69.1 * (zc.Lat - :zipLat), 2 ) + 
         power( 69.1 * (zc.Lng - :zipLng) * cos(:zipLat/57.3), 2) ) <= :maxDistance 
         order by sqrt( power( 69.1 * (zc.Lat - :zipLat), 2 ) + 
         power( 69.1 * (zc.Lng - :zipLng) * cos(:zipLat/57.3), 2)  ) 

Distance is calculated, but I need to sort/order by it. I tried the following HQL, but I think I'm barking up the wrong tree. Order by cannot contain Arithmetic operators. Any suggestions? :

Code:

         select ci 
         from Store as ci, Zipcode as zc 
         where ci.Zipcode = zc and 
         sqrt( power( 69.1 * (zc.Lat - :zipLat), 2 ) + 
         power( 69.1 * (zc.Lng - :zipLng) * cos(:zipLat/57.3), 2) ) <= :maxDistance 
         group by ci 
         order by max( sqrt( power( 69.1 * (zc.Lat - :zipLat), 2 ) + 
         power( 69.1 * (zc.Lng - :zipLng) * cos(:zipLat/57.3), 2)  ) )

Thanks much!

Top

luxspes

Post subject: Have you tried with a SubQuery

Posted: Tue Apr 25, 2006 2:41 pm

Regular

Joined: Fri Jul 29, 2005 9:46 am
Posts: 101

Hi!
Have you tried with a subquery?

select * from(select * from ...) as complexsubquery
order by complexsubquery.complexalreadycalculatedfield

i think it should work...

Top

Page 1 of 1

[ 3 posts ]

Board index » Hibernate & Java Persistence » NHibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum