Hello,

Why doesn't this HQL work with Oracle ?



Exception is: java.sql.SQLException: ORA-00979: not a GROUP BY expression

Thanks!

I doubt that this query works with any database. It is translated to something similar to



If you are grouping by year, month and day you can't select *. Which row of the grouped ones would be selected? I know that for example, PostgreSQL will also complain with an error message, but MySQL will select a random row from each group.

I tested on MySQL and it works; It selects the first entry foreach group;

Could it be that Oracle doesn't go this way ?

In my opinion this doesn't work in MySQL either. You just get a "random" row in the result instead of an exception. See http://dev.mysql.com/doc/refman/5.0/en/ ... ields.html

nordborg, thanks for your anwser !

I only want to know on which days do I have entries, so the order does not matter here.

So, how should I write the select query in order to return a <Test> list ? Is there another way than using subqueries ? I thought about somethink like:



Thanks!

The simplest query would be to just select the year, month and day as an Object[].



If you need it to return Test objects the query you posted should work. Another option is to use the "constructor" syntax as described in http://www.hibernate.org/hib_docs/v3/re ... elect.html

Thanks, nordborg !

Unfortunately, another problem occured :(
Hibernate cannot (or at least I cannot figure out how to) convert properly a HQL to Oracle SQL when trying to extract the hour/minute/second value for a date field;

error is: ORA-30076 invalid extract field for extract source

Any ideea ?

I have not worked with Oracle so I have no idea what the error message means. Maybe if you post the generated SQL.

generated code:

...

...


Hibernate does not know/check that the field is of type date or timestamp, and it generates this invalid code, which is ok only for timestamp fields;

Any ideea ? Is changing the field type to timestamp the only solution ?

Why are you trying to extract hour and minutes from a field that doesn't contain that information?

as far as I know, Oracle date fields contain date/time information...

I got the same situation. I created a question in StackOverflow. Apparently, there's no way to make it work. What I think you can do is to extend the Oracle's dialect and register the extract function to use something that works.