Can anybody tell me what is wrong with the following query:

select ord.id
from test.Order ord
join ord.orderItems item
where (ord.orderDate < '2001-01-01' AND item.amount < 100)
AND sum(item.price) < 1000

hibern8ide gives the following error message:

1 errors occurred while listing (and calling getPathNames).
net.sf.hibernate.JDBCException: Could not execute query
java.sql.SQLException: General error in statement [select order0_.ID as x0_0_ from ORDERS order0_ inner join ORDER_ITEMS orderitems1_ on order0_.ID=orderitems1_.ORDER_ID where ((order0_.ORDER_DATE<'2001-01-01' )AND(orderitems1_.AMOUNT<100 ))AND(sum(orderitems1_.PRICE)<1000 )]

The strange thing is that if I add "join item.orderItemDetails detail" with no other changes, it succeeds.

Thanks for any pointers. I've read the hql chapter of the manual, but there weren't any examples there that were close to what I want to do.

i also tried the following:

select ord.id
from test.Order ord
join ord.orderItems item
where (ord.orderDate < '2001-01-01' AND item.amount < 100)
AND 1000 > (select sum(item.price) from item)

and the error message is:

1 errors occurred while listing (and calling getPathNames).
net.sf.hibernate.JDBCException: Could not execute query
java.sql.SQLException: Table not found: ) in statement [select order0_.ID as x0_0_ from ORDERS order0_ inner join ORDER_ITEMS orderitems1_ on order0_.ID=orderitems1_.ORDER_ID where ((order0_.ORDER_DATE<'2001-01-01' )AND(orderitems1_.AMOUNT<100 ))AND(1000>(select sum(orderitems1_.PRICE) from))]

Table not found

1). I guess you are using DBMS with case sensitive SQL names, read the following chapter 5.3. SQL quoted identifiers http://www.hibernate.org/hib_docs/reference/en/html/mapping.html#mapping-quotedidentifiers
and also look at the following topic problem with case sensitive database http://forum.hibernate.org/viewtopic.php?t=930754

2). And also check your mapping :)