I have the problem that I can not use "as" in a select statement of a query I use. As soon as I do not use "as" everything is fine. But in fact I need "as" because I want to have an "order by" statement for one of the attributes I am querying for.
Any help is welcome.

Regards
Guido

Hibernate version: 3.0

Code between sessionFactory.openSession() and session.close():
This fails (see exception below):

Query query = session.createQuery("select sum(s.requestCount) as sum, c.description "
+ "from " + getSnapshotClass().getName() + " s, " + getCountedEntityClass().getName() + " c "
+ "where s.projectId=" + getProjectId() + " and "
+ "s.refObjId = c.id and "
+ "s.period = " + getPeriod() + " and "
+ "s.start >= :start and "
+ "s.start <= :end "
+ "group by s.refObjId ");
query.setDate("start", getTimeFrameStart().getTime());
query.setDate("end", getTimeFrameEnd().getTime());

This works:

Query query = session.createQuery("select sum(s.requestCount), c.description "
+ "from " + getSnapshotClass().getName() + " s, " + getCountedEntityClass().getName() + " c "
+ "where s.projectId=" + getProjectId() + " and "
+ "s.refObjId = c.id and "
+ "s.period = " + getPeriod() + " and "
+ "s.start >= :start and "
+ "s.start <= :end "
+ "group by s.refObjId ");
query.setDate("start", getTimeFrameStart().getTime());
query.setDate("end", getTimeFrameEnd().getTime());


Full stack trace of any exception that occurs:
org.hibernate.hql.ast.QuerySyntaxError: unexpected token: as near line 1, column 28 [select sum(s.requestCount) as sum, c.description from org.upserve.model.BrowserSnapshot s, org.upserve.model.Browser c where s.projectId=1 and s.refObjId = c.id and s.period = 1 and s.start >= :start and s.start <= :end group by s.refObjId ]
org.hibernate.hql.ast.ErrorCounter.throwQueryException(ErrorCounter.java:63)
org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:215)
org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:127)
org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:83)
org.hibernate.impl.SessionFactoryImpl.getQuery(SessionFactoryImpl.java:414)
org.hibernate.impl.SessionImpl.getQueries(SessionImpl.java:821)
org.hibernate.impl.SessionImpl.list(SessionImpl.java:779)
org.hibernate.impl.QueryImpl.list(QueryImpl.java:74)
org.upserve.upservatory.model.DistinctionModel.update(DistinctionModel.java:55)
org.upserve.upservatory.action.AbstractUpdateTimeFramedCountedEntityViewAction.execute(AbstractUpdateTimeFramedCountedEntityViewAction.java:58)
org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:419)
org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:224)
org.apache.struts.action.ActionServlet.process(ActionServlet.java:1194)
org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:432)
javax.servlet.http.HttpServlet.service(HttpServlet.java:709)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
org.netbeans.modules.web.monitor.server.MonitorFilter.doFilter(MonitorFilter.java:362)

Name and version of the database you are using: MySQL

It's probably not the as making problems, but your alias!
Sum is a keyword.

Just take another one and I'm quite sure it'll work.

Best regards
Sven

That is what I also thought, but it is not the reason. It alos fails with other aliases. And the exception also says that "as" is an unexpected token.

Any other ideas?

Have you already tried to use the join syntax?
http://www.hibernate.org/hib_docs/v3/re ... yhql-joins

Well, I just read again what you wrote:


I think it's not possible to reuse an alias of the select clause in the order by clause. Sounds strange, but as far as I can remember at least Oracle was not able to do this.

Example:


You'll have to repeat the expression.


Though, an expression is not allowed in the order by clause for some databases (e.g. MySQL).
If you're sure, whether your database can handle such a case, you might think about the need for an alias after sum(..).
However, I'd also be interested in getting to know why it does not work...

Best regards
Sven

Thanks for your reply. However, I want to make my app as db independant as possible. Thus, I can not use sum(x) in the order clause as this raises in exception when using MySQL.

Maybe I found a bug?
Is there anyone out there that successfully used an alsias for a sum in a select statement?

I don't know, if there's an abstraction of this in Hibernate meaning that Hibernate decides how to handle an expression with order by.

The documentation says: "SQL functions and aggregate functions are allowed in the having and order by clauses, if supported by the underlying database (eg. not in MySQL)."
http://www.hibernate.org/hib_docs/v3/re ... l-ordering

Probably you won't be able to be database-independent in this case.




Maybe. Please verify this again, build a simple test case and put this issue to Jira.

Best regards
Sven

After some research I am really desperately. It seems that using as does not work at all (at least not in my app).

This works:
session.createQuery("select sum(s.requestCount) from BrowserSnapshot s");

This not:
session.createQuery("select sum(s.requestCount) as t from BrowserSnapshot s");

Exception:
org.hibernate.hql.ast.QuerySyntaxError: unexpected token: as near line 1, column 28 [select sum(s.requestCount) as t from org.upserve.model.BrowserSnapshot s]

And even worse:

This works:
session.createQuery("select s.requestCount from BrowserSnapshot s");

This not:
session.createQuery("select s.requestCount as t from BrowserSnapshot s");

Exception:
org.hibernate.hql.ast.QuerySyntaxError: unexpected token: as near line 1, column 23 [select s.requestCount as t from org.upserve.model.BrowserSnapshot s]

Could it be because of sunclass hierarchy? My mapping is like this:
<class name="org.upserve.model.CountedEntitySnapshot" table="Snapshot">
<id name="id" column="id">
<generator class="increment"/>
</id>
<discriminator
column="type"
type="string"
/>
<property name="refObjId"/>
<property name="requestCount"/>
<subclass name="org.upserve.model.BrowserSnapshot" discriminator-value="bro"/>
</class>

what version are you using (the *exact* version). 'as' in the select clause was added later in H3 development; check jira/changelog for the exact version it was added.

Thanks a lot. That was the problem. I was using an early 3.0 version. Now I switched to 3.0.5 which solved the problem.

Regards and thanks again
Guido