• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

converting from sql to hql

 
Ravikiran Reddy Baddam
Greenhorn
Posts: 10
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
hello all.
i have SLQquery
which i need to convert into HQL

"WITH a as
(select asset_id,max(last_updated) last_updated from sl_Asset_History group by asset_id)
SELECT sah.asset_Id,sah.MAP_HIERARCHY FROM sl_Asset_History sah ,a
where a.asset_Id=sah.asset_Id and a.last_Updated=sah.last_Updated";


when I run this query from HQL I am getting error as unexpected token WITH

could you please share me the ideas for this

Thank you for your help.
 
Winston Gutkowski
Bartender
Pie
Posts: 10571
64
Eclipse IDE Hibernate Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ravikiran Reddy Baddam wrote:could you please share me the ideas for this

The basic problem (as it ALWAYS is with SQL) is that 'WITH' is not part of the "basic" language (I had to look it up). According to this article it was introduced with the SQL-99 standard, and may well not be supported by all database platforms (which is, almost certainly, a requirement for HQL).

It's basically a piece of syntactic sugar to allow the query processor to pre-process a sub-query that may, logically, be required many times and, while I have no particular problem with the concept, it's just one of the reasons that I curse SQL and - almost - everything it stands for. And I say that as a former database administrator and modeller.

However, getting back to your problem: the solution. My suggestions:
1. Find a good HQL forum (we may even have one), and ask your question there.
2. Imagine that WITH doesn't exist, and rewrite your query on that basis. It can be done, and it will probably be slower (and bigger), but at least it will translate into HQL more easily.

HIH

Winston
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It all depends on what you want to do. Do you really want to convert this query into JPQL (or HQL)? Or do you want to use this query as a native query but use the benefits of an ORM?

In both cases you'll need to create entities. In short, entities map your database schema into classes. So when you execute a query, object(s) are returned (instead of a ResultSet which you have to process). Both aforementioned options will definitely be possible. But you definitely need your entities, because when you want to write JPQL (or HQL), you'll refer to fields of these entities (instead of column names).

The Java Persistence API WikiBook is an excellent resource and when I'm having an issue with JPA, JPQL, or something related it's the first resource I'll check to find a solution. It's really awesome!

Hope it helps!
Kind regards,
Roel
 
Winston Gutkowski
Bartender
Pie
Posts: 10571
64
Eclipse IDE Hibernate Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Roel De Nijs wrote:It all depends on what you want to do. Do you really want to convert this query into JPQL (or HQL)? Or do you want to use this query as a native query but use the benefits of an ORM?
In both cases you'll need to create entities...

Hmmm. Doesn't sound like the "nirvana" I was promised when I learnt SQL back in the early '80s...

Don't get me wrong, I love RDBMS's (I was a DBA for 15 years). It's the "QL" part that I have problems with - and I knew it back in '83 when I was taught it - but I didn't know then what it was I disliked.

Now I do. So prepare for an "SQL - How mathematics can actually be crap" thread.

Winston
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic